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

User · Accepted Answer

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

User · Answer

I know its been a while since the original post but I like using CTE s and this worked for me   WITH cte table a AS       SELECT  id   id        MAX  value    value      FROM table a     GROUP BY  id    UPDATE table b SET table b code   CASE WHEN cte table a  value  IS NOT NULL THEN cte table a  value  ELSE 124 END FROM table b LEFT OUTER JOIN  cte table a ON table b id   cte table a id

User · Answer

Try this   Update TableB Set   Code   Coalesce       Select Max Value      From TableA      Where Id   b Id   123  From TableB b

[sql] SQL Server: IF EXISTS ; ELSE

Examples related to sql

Examples related to sql-server

Examples related to if-statement

Examples related to exists