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 !
The IF EXISTS decision structure will execute a block of SQL code only if an inner query returns one or more rows. If the inner query returns an empty result set, the block of code within the structure is skipped. The inner query used with the IF EXISTS structure can be anything you need it to be.
In SQL Server, the IF...ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.
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.
Try this:
Update TableB Set Code = Coalesce( (Select Max(Value) From TableA Where Id = b.Id), 123) From TableB b
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With