Check if exists within SQL CASE statement

Question

I am trying to update a column in table a based on whether a different column in the table is in a set of results from table b. Currently variations on:

update a
set a.field1 =
 case
 when exists (
   select b.field2
   from b
   where b.field2 = a.field2
 )
 then 'FOO'
 else 'BAR'
 end

are not running. Any ideas how to do this for a DB2 database?

Edit: Thanks for your answers, best I can do is

update a set field1 = 'FOO' where field2 in (select field2 from b);

update a set field1 = 'BAR' where field2 not in (select field2 from b);

But I'll leave this open in case someone can find a version of the code at the top that works.

Solution 1

I work on a DB2 for iSeries box. Try this:

update a
set a.field1 =
 Coalesce( ( select 'FOO'
             from b
             where b.field2 = a.field2 ),
                      'BAR' )

Coalesce() is a function that returns the first non NULL in the list.

Solution 2

This works in SQLServer. Perhaps DB2 has a similar construction.

update a SET field1 = 'BAR'
from a
     left outer join b on b.field1 = a.field1
where b.field1 is null;
update a SET field1 = 'FOO'
from a
     inner join b on b.field1 = a.field1

Regards,
Lieven

Comments

[Tom H]

That will do the trick. You could still put it into one update statement with a CASE statement checking for NULLs in the PK of table B, but there can also be advantages of doing it in two steps and what you have now eliminates the duplicate updates.

[nearly_lunchtime]

Syntax doesn't seem to work in DB2, but you're correct in that the spirit of just doing it in two separate runs is clearer.

[nearly_lunchtime]

Valid syntax (ie DB2 doesn't like updating joins) is update a set field1 = 'FOO' where field2 in (select field2 from b); update a set field1 = 'BAR' where field2 not in (select field2 from b); I'll accept if you mention that!

[Lieven Keersmaekers]

@Nearly_lunchtime. What would you like me mentioned? You've found the solution yourself. You can answer your own question and accept that? Thank you for suggesting it though.