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