基于连接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      |
|-------------------------------------|

auth

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

auth

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

(正如比尔·卡文所建议的，你的表名是na而不是oa)。