Update table through using multiple joins MSSQL

Question

I have the following two tables

activity

activity_bak

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.

Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Tab Alleman, May 17 '16 at 17:56

score -1 · Accepted Answer · answered May 17 '16 at 17:06

-1

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

UPDATE a
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;

answered May 17 '16 at 17:06

Lamak

69,480
12
108
116

Ah. I should've known that. Thanks! – ghoston3rd May 17 '16 at 17:06

score -1 · Answer 2 · answered May 17 '16 at 17:08

-1

I think you want:

update A 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
        And ab.activity_seq = a.activity_seq

answered May 17 '16 at 17:08

Joe C

3,925
2
11
31

Update table through using multiple joins MSSQL

2 Answers2