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 I want to populate col = code of table B if there exists ID = 2 in tableA. for multiple values , get max value. else populate it with '123'. Now here is what I used:

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 

I am sure there is some problem in BEGIN;END or in IF EXIST;ELSE. Basically I want to by-pass the else part if select statement in IF-part exist and vice- versa. For example if select statement of IF=part is:

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

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

Answer 1

EDIT

I want to add the reason that your IF statement seems to not work. When you do an EXISTS on an aggregate, it's always going to be true. It returns a value even if the ID doesn't exist. Sure, it's NULL, but its returning it. Instead, do this:

if exists(select 1 from table where id = 4) 

and you'll get to the ELSE portion of your IF statement.

---

Now, here's a better, set-based solution:

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.

Answer 2

Try this:

Update TableB Set   Code = Coalesce(     (Select Max(Value)     From TableA      Where Id = b.Id), 123) From TableB b