SQL Server IF EXISTS ELSE

Question

I have a tableA:

ID value
 1  100
 2  101
 2  444
 3  501

Also TableB

ID Code
1
2

Now, if ID = 2 is present in tableA, I want to fill in col = code of table B. Get the maximum value for all values. otherwise, fill it with "123." Here is what I employed:

if exists (select MAX(value) from #A where id = 2)
 BEGIN
 update #B
 set code = (select MAX(value) from #A where id = 2)
 from #A
 END

 ELSE 

 update #B
 set code = 123
 from #B

There must be some issue with BEGIN;END or IF EXIST;ELSE. In essence, I want to skip the else section of the if choose statement and vice versa. Assuming that the choose statement of the IF=part is:

(select MAX(value) from #A where id = 4)

It should just populate 123, coz ID = 4 do not exist ! 

Can someone please help me with this?

Answer 1

Try this:

update b
  set code = isnull(a.value, 123)
from #b b
left join (select id, max(value) from #a group by id) a
  on b.id = a.id
where
  b.id = yourid

This has the benefit of being able to run on the entire table rather than individual ids.