DB2 Update/Delete With Jion

13432878738

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 dummy
where not exists (select 1 from mytable where mycolumn='12345')