[sql] INNER JOIN in UPDATE sql for DB2

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

Update to the answer https://stackoverflow.com/a/4184237/565525:

if you want multiple columns, that can be achived like this:

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

Source: http://www.dbforums.com/db2/1615011-sql-update-using-join-subquery.html#post6257307

for you ask

update file1 f1
set file1.firstfield=
(
select 'BIT OF TEXT' || f2.something
from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
where exists
(
select * from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
and f1.firstfield like 'BLAH%'

if join give multiple result you can force update like this

update file1 f1
set file1.firstfield=
(
select 'BIT OF TEXT' || f2.something
from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
fetch first rows only
)
where exists
(
select * from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
and f1.firstfield like 'BLAH%' 

template methode

update table1 f1
set (f1.field1, f1.field2, f1.field3, f1.field4)=
(
select f2.field1, f2.field2, f2.field3, 'CONSTVALUE'
from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2) 
)
where exists 
(
select * from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2)
) 

Just to update only the rows that match the conditions, and avoid updating nulls in the other rows:

update table_one set field_1 = 'ACTIVE' where exists 
(select 1 from table_two where table_one.customer = table_two.customer);

It works in a DB2/AIX64 9.7.8

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%'

In standard SQL this type of update looks like:

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

With minor syntactic variations this type of thing will work on Oracle or SQL Server and (although I don't have a DB/2 instance to hand to test) will almost certainly work on DB/2.

Here's a good example of something I just got working:

update cac c
set ga_meth_id = (
    select cim.ga_meth_id 
    from cci ci, ccim cim 
    where ci.cus_id_key_n = cim.cus_id_key_n
    and ci.cus_set_c = cim.cus_set_c
    and ci.cus_set_c = c.cus_set_c
    and ci.cps_key_n = c.cps_key_n
)
where exists (
    select 1  
    from cci ci2, ccim cim2 
    where ci2.cus_id_key_n = cim2.cus_id_key_n
    and ci2.cus_set_c = cim2.cus_set_c
    and ci2.cus_set_c = c.cus_set_c
    and ci2.cps_key_n = c.cps_key_n
)

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.

The reference documentation for the UPDATE statement on DB2 LUW 9.7 gives the following example:

   UPDATE (SELECT EMPNO, SALARY, COMM,
     AVG(SALARY) OVER (PARTITION BY WORKDEPT),
     AVG(COMM) OVER (PARTITION BY WORKDEPT)
     FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
   SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
   WHERE EMPNO = '000120'

The parentheses after UPDATE can contain a full-select, meaning any valid SELECT statement can go there.

Based on that, I would suggest the following:

UPDATE (
  SELECT
    f1.firstfield,
    f2.anotherfield,
    f2.something
  FROM file1 f1
  WHERE f1.firstfield like 'BLAH%' 
  INNER JOIN file2 f2
  ON substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
AS my_files(firstfield, anotherfield, something)
SET
  firstfield = ( 'BIT OF TEXT' || something )

Edit: Ian is right. My first instinct was to try subselects instead:

UPDATE file1 f1
SET f1.firstfield = ( 'BIT OF TEXT' || (
  SELECT f2.something
  FROM file2 f2
  WHERE substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
))
WHERE f1.firstfield LIKE 'BLAH%' 
AND substr(f1.firstfield,10,20) IN (
  SELECT substr(f2.anotherfield,1,10)
  FROM file2 f2
)

But I'm not sure if the concatenation would work. It also assumes that there's a 1:1 mapping between the substrings. If there are multiple rows that match, it wouldn't work.

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)