ghoston3rd ghoston3rd - 2 years ago 84
SQL Question

Update table through using multiple joins MSSQL

I have the following two tables


enter image description here


enter image description here

I want to take the comments from activity_bak and update the comments in activity to match by using the corresponding activity_no and activity_seq.

I've tried doing it like this but to no success:

update Animal.sysadm.activity
set activity_comment = ab.activity_comment
from Animal.SYSADM.activity a
left join Animal.SYSADM.activity_bak ab
on ab.activity_no = a.activity_no
left join Animal.sysadm.activity_bak ab2
on ab2.activity_seq = a.activity_seq

Any help or pointers would be greatly appreciated.

Answer Source

No need to do 2 joins, you need just one. The right syntax is:

SET a.activity_comment = ab.activity_comment
FROM Animal.SYSADM.activity a
INNER JOIN Animal.SYSADM.activity_bak ab
    ON ab.activity_no = a.activity_no
    AND ab.activity_seq = a.activity_seq;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download