0

I'm getting an error when trying an update with a join in db2 that 'inner' is not expected and that it is instead expecting 'set'.

Looking at the query I understand what it is saying but I'm confused: Can you not do a join in an update on DB2?

I'm usually using MySQL but this script has to run against a db2 database and I've never come across this issue but it seems like it should be correct using db2 for iseries 7.2

UPDATE data d
    inner join sales s
    ON d.sku_id = s.id
SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
    quantity = cast(:QUANTITY as int)
WHERE d.custID = cast(:cust as int)
Geoff_S
  • 4,917
  • 7
  • 43
  • 133
  • Use the MERGE statement instead. – mao Nov 20 '18 at 14:57
  • Possible duplicate of [How to update DB2 table with a join?](https://stackoverflow.com/questions/20022982/how-to-update-db2-table-with-a-join) – mao Nov 20 '18 at 14:58
  • I looked at it but the format looks a little different. How exactly would I use a merge on this? i've only used a merge for upserts before – Geoff_S Nov 20 '18 at 15:44
  • Possible duplicate of [SQL update from one Table to another based on a ID match IN db2](https://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2) – mustaccio Nov 20 '18 at 16:52
  • I posted my IRL answer of SQL that works on a variety of DB flavors – danny117 Nov 20 '18 at 17:19

2 Answers2

1

Exists clause can be used on DB2 for Iseries update statement. I just took the join and moved it to a where exists clause. Your only updating one table with supplied values so this was easy transformation.

    UPDATE data d
        SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
        quantity = cast(:QUANTITY as int)
    WHERE d.custID = cast(:cust as int)
    and exists ( 
    select s.id from 
     sales s
      where d.sku_id = s.id
    )
danny117
  • 5,581
  • 1
  • 26
  • 35
0

I think you can use a from clause in DB2:

UPDATE data d
    SET expire_date = (to_date(:SHIPDATE, 'YYYYMMDD') + 127 DAYS) ,
        quantity = cast(:QUANTITY as int)
FROM sales s
WHERE d.sku_id = s.id AND
      d.custID = cast(:cust as int);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786