IBM DB2 update using JOIN

Question

I need to update a table containing orders depending on customer information. This is how i would have approached it but apparently DB2 doesnt support JOINs in UPDATEs. I am working on an IBM iSeries.

UPDATE lib.orders as o
  JOIN lib.customers as c
  ON   o.cstmrid = c.id
SET    o.updatehere = 'NEWVALUE' 
WHERE  c.info = 'VALUE'

There are allready questions on that topic but none really help me.

Thanks!

Answer 1

Okay so this is really late but in case someones reading this: none of the comments/answers were correct. The important point is that i am working on an iseries which uses db2 udb and does neither support joins on updates nor merge (at least the version we work with). The only way i figured out will work is a WHERE EXISTS clause.

Answer 2

I would recommend something like:

UPDATE lib.orders
SET updatehere = 'NEWVALUE'
WHERE cstrmid in (
  SELECT id
  FROM lib.customers
  WHERE info = 'VALUE'
)

The reality is that an update statement has four main pieces:

1. what table is being updated?
   • possibly with an alias specified
   • your example used one
   • my suggestion didn't need one
2. what field(s) is/are being updated within item 1?
   • can be a single field, as in your example
   • can be a tuple, containing multiple fields
3. what value(s) is/are being populated into item 2?
   • can be a single value or a tuple of values, depending on item 2
   • can be the result of a subquery
     • must return a single value or a tuple, as needed, to match item 2
     • can join against the table specified in item 1 to ensure different values are output for different rows in the item 1's table
4. what matching criteria are there for records in item 1?
   • can be one or more criteria
   • if you don't specify this, DB/2 will iterate through ALL records in the table
   • can include a subquery
     • if you have a subquery in item 3, and a row matching this doesn't match up with any results from that subquery, item 2's fields will be assigned null values

I specified item 1 in line 1 of my suggestion.

I specified items 2 and 3 on line 2 of my suggestion.

I specified item 4 using a subquery in lines 3 - 7

I have, on occasion, written update statements where:

• item 2 was a tuple of multiple fields
• item 3 was a subquery which returned multiple values, joining against the table specified in item 1
• item 4 used a subquery similar to what was used in item 3 but returning join values instead of update values for fields

The result was that DB/2 queried against item 1, using item 4 as criteria and then iterated through the resulting rows, updating fields in item 2 against values returned from item 3. As item 3 was a subquery, the join against the table alias specified in item 1 provided necessary criteria for what was returned from the subquery.

I've written update statements where the subqueries in question were Table Value Constructors. In that fashion, I was able to update a hundreds of records based on a small set of known values.

I have done all of this on DB/2 on an iSeries. I never used a MERGE because, as you've noticed, not all versions of DB/2 support that.