Posted By: Anonymous
I have a tableA:
ID value 1 100 2 101 2 444 3 501
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 !
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
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.