Update statement is giving error for table alias

Question

I am unable to figure out what is wrong with the following syntax.

alter table #table1 add C21 datetime
update #table1 a
set a.C21 = b.C21_result_lab from #table2 b where a.person_id = b.person_id and 
a.source_id = b.source_id

Error message:

Msg 102, Level 15, State 1, Line 363 Incorrect syntax near 'a'.

Answer 1

This is the correct syntax:

alter table #table1 add C21 datetime;
update a
set a.C21 = (
  select b.C21_result_lab from #table2 b 
  where 
    a.person_id = b.person_id 
    and 
    a.source_id = b.source_id
)
from #table1 a

You must make sure that the select query does not return more than 1 rows.
Or you can do it with a join:

update a
set a.C21 = b.C21_result_lab 
from #table1 a inner join #table2 b 
on a.person_id = b.person_id and a.source_id = b.source_id