CODEWITHSUNDEEP
mysqljoinsql-update
cppcoder
cppcoder

Reputation: 23095

Update a table based on joining 4 tables

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

Upvotes: 0

Views: 37

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

In mysql you could use this sintax but you have not an id in you from ( select ) oa .. i have added the o.id for this (hope is the right one)

    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)

Upvotes: 1

Related Questions