DB2 Update/Delete With Jion

luzl

When you need to update one table based on the other table -
it is called "correlated update".  You have to repeat the same "where"
logic 2 times.

update maintab m set (m.fname, m.lname) =  (select u.fname,u.lname from updatetab u where m.id=u.id) where exists  (select null from updatetab u where m.id=u.id);

or variation: use "in" expression:

update maintab m set (m.fname, m.lname) = (select u.fname,u.lname from updatetab u where m.id=u.id) where m.id in  (select u.id from updatetab);

or delete expression:
delete from maintab m where exists  (select null from updatetab u where m.id=u.id);  

Note: If you don't include the 2nd "where" clause - then  ALL rows in the
main table will be updated (putting NULLs in all rows which are not part of the join)
======================
Please note: the following Sybase syntax DOES NOT WORK in DB2:
                update maintab                set m.fname=u.fname, m.lname=u.lname                from  maintab m, updates u                where m.id=u.id
======================

How to insert a row - but only if the row with the value of a file doesn't exist:
insert into mytable (mycolumn) select '12345' from table (values 1) as dummywhere not exists (select 1 from mytable where mycolumn='12345')