CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5107

joining in an update in db2

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)

Upvotes: 0

Views: 176

Answers (2)

danny117
danny117

Reputation: 5651

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
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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);

Upvotes: 0

Related Questions