INNER JOIN in UPDATE sql for DB2

Question

Is there a way to use joins in update statements for DB2?

Google has really let me down on this one

This is roughly what I'm trying to achieve (... except obviously working ....)

update file1 inner join file2                                 
       on substr(file1.firstfield,10,20) = substr(file2.anotherfield,1,10)                                                                    
set file1.firstfield = ( 'BIT OF TEXT' concat file2.something )                                                                             
where file1.firstfield like 'BLAH%'                             

Cheers

Answer 1

You don't say what platform you're targeting. Referring to tables as files, though, leads me to believe that you're NOT running DB2 on Linux, UNIX or Windows (LUW).

However, if you are on DB2 LUW, see the MERGE statement:

For your example statement, this would be written as:

merge into file1 a
   using (select anotherfield, something from file2) b
   on substr(a.firstfield,10,20) = substr(b.anotherfield,1,10)
when matched and a.firstfield like 'BLAH%'
   then update set a.firstfield = 'BIT OF TEXT' || b.something;

Please note: For DB2, the third argument of the SUBSTR function is the number of bytes to return, not the ending position. Therefore, SUBSTR(a.firstfield,10,20) returns CHAR(20). However, SUBSTR(b.anotherfield,1,10) returns CHAR(10). I'm not sure if this was done on purpose, but it may affect your comparison.