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
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).