DB2 UPDATE with INNER JOINS


Unlike SQL Server, DB2 does not allow joins within its update statement.  For example

UPDATE T1
SET T1.COL1 = 'NEW VALUE'
FROM TABLE01 T1
INNER JOIN TABLE02 T2
  ON T1.KEYCOL = T2.KEYCOL
INNER JOIN TABLE03 T3
  ON T2.KEY2COL = T3.KEYCOL
WHERE T1.COL2 = 'SOME VALUE'

To accomplish the same thing in DB2 you would have to do the following:

UPDATE TABLE01 T
SET T.COL = 'NEW VALUE'
WHERE EXISTS
(
  SELECT *
  FROM TABLE01 T1
  INNER JOIN TABLE02 T2
   ON T1.KEYCOL = T2.KEYCOL
  INNER JOIN TABLE03 T3
   ON T2.KEY2COL = T3.KEYCOL
  WHERE T1.COL2 = 'SOME VALUE'
  AND T.KEYCOL = T1.KEYCOL
)

Essentially you write the query that would produce the rows that you want to update. Nest that query and execute a simple update statement with an EXISTS clause

Leave a comment

Filed under Sharing Stuff

Comments are closed.