INNER JOIN in UPDATE sql for DB2

Solution 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.

Solution 2:

Joins in update statements are non-standard and not supported by all vendors. What you're trying to do can be accomplished with a sub-select:

update
  file1
set
  firstfield = (select 'stuff' concat something from file2 where substr(file1.field1, 10, 20) = substr(file2.xxx,1,10) )
where
  file1.foo like 'BLAH%'

Solution 3:

Try this and then tell me the results:

UPDATE File1 AS B                          
SET    b.campo1 = (SELECT DISTINCT A.campo1
                   FROM  File2 A           
                   INNER JOIN File1      
                   ON A.campo2 = File1.campo2 
                   AND A.campo2 = B.campo2)