mysql - 基于连接 4 个表更新一个表

Question

我有下表:

代理

+-------------------------------------+
| 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      |
|-------------------------------------|

授权

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

auth_old

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

我需要这样的结果表:

代理

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

授权

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

这是我已经得到但没有运行的:

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

Answer 1

在mysql中你可以使用这个sintax，但是你没有来自(select)oa的id..我已经为此添加了o.id(希望是正确的)

    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 

(按照 Bill Karvin 的建议，您使用了错误的表别名 na 而不是 oa)