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.

Answer 1

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

Answer 2

The first occurence of a.field1 should be a.field2.

You said "whether a different column in the table is in a set..."

Your code is modifying the same column, not a different column.

Answer 3

I'm not an expert on SQL or DB2 but maybe you can join the two tables and check wheither b.field1 is null??

update a set a.field1 = case when b.field1 is not null then 'FOO' else 'BAR' end from a full outer join b on a.field1 = b.field1

Good luck

Answer 4

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.