Update a table based on joining 4 tables

Question

I have the following tables:

agent

+-------------------------------------+ | id | name | desc | comment | |-------------------------------------+ | 1 | agent1 | agent 1 | sss | | 2 | agent2 | agent 2 | eee | |-------------------------------------|

agent_old

+-------------------------------------+ | id | name | desc | comment | |-------------------------------------+ | 1 | agent1 | agent 1 | sss | | 2 | agent3 | agent 3 | eee | |-------------------------------------|

auth

+-------------------------------+ | id | login | password | |-------------------------------+ | 1 | agent1 | xxxxxxx | | 2 | agent2 | yyyyyy | |-------------------------------|

auth_old

+-------------------------------+ | id | login | password | |-------------------------------+ | 1 | oldagent1 | wwwwww | | 2 | oldagent2 | qqqqqq | |-------------------------------|

I need the resultant tables like this:

agent

+-------------------------------------+ | id | name | desc | comment | |-------------------------------------+ | 1 | agent1 | agent 1 | sss | | 2 | agent2 | agent 2 | eee | |-------------------------------------|

auth

+-------------------------------+ | id | login | password | |-------------------------------+ | 1 |oldagent1 | wwwwww | | 2 | agent2 | yyyyyy | |-------------------------------|

This is what I have got but does not run:

update auth a set a.login = oa.login, a.password = oa.password from ( select o.login, o.password from auth_old o join agent na join agent_old ago on ago.id = o.id and na.name = ago.name and na.desc = ago.desc ) oa where a.id = na.id

That looks like implicit join notation with it's comma's replaced with "join"; it really is a good practice to get used to modern explicit join notation. — Uueerdo
– Uueerdo, Commented Jan 28, 2019 at 19:46

halfer · Accepted Answer · 2025-10-26 11:34:25Z

1

In MySQL you could use this syntax but you have not an id in your from ( select ... ) oa. I have added the o.id for this:

 update auth a inner join ( select o.login, o.password , na.id from auth_old o join agent na join agent_old ago on ago.id = o.id and na.name = ago.name and na.desc = ago.desc ) oa on a.id = oa.id set a.login = oa.login, a.password = oa.password

(And as suggested by Bill Karvin you have a wrong table alias na instead of oa).

edited Oct 26 at 11:34

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Jan 28, 2019 at 19:31

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

cppcoder Over a year ago

It should be na.id not oa.id. Not sure how to achieve. id matching should only happen between agent and auth or agent_old and auth_old

ScaisEdge Over a year ago

@cppcoder .. once you have create the inner join ( ) table_alias the reference for the column in the inner select must be based on the table_alias .. so the ON condition can't be na.id but oa.column_name (oa is your table_alias)

cppcoder Over a year ago

I changed o.id to na.id in the select clause and used it to achieve my need

ScaisEdge Over a year ago

. have update the answer with na.id .. .. seems that you rarerly ..mark the answer as acpted or rate as usefult .. seems not fair this me ..

cppcoder Over a year ago

Not sure why do you think that way. I have accepted answer for all the questions I ask and has got correct answer.

|

Collectives™ on Stack Overflow

Update a table based on joining 4 tables

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related