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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] INNER JOIN in UPDATE sql for DB2

Examples related to sql

Examples related to db2