Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: IF EXISTS ; ELSE

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 !

like image 807
Bhupinder Singh Avatar asked Sep 15 '11 02:09

Bhupinder Singh


People also ask

How do you use EXISTS with if in SQL?

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.

Can we use else if in SQL Server?

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.


2 Answers

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.

like image 196
Derek Kromm Avatar answered Nov 06 '22 22:11

Derek Kromm


Try this:

Update TableB Set   Code = Coalesce(     (Select Max(Value)     From TableA      Where Id = b.Id), 123) From TableB b 
like image 26
Charles Bretana Avatar answered Nov 06 '22 20:11

Charles Bretana