DB2 Update/Delete With Jion
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 - thenALL 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 frommaintab 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')
页:
[1]