CODEWITHSUNDEEP
db2ibm-midrangedb2-400
Cody Geisler
Cody Geisler

Reputation: 8617

UPDATE statement only works with FETCH FIRST

I am trying to update a table of serials with their associated order's create date. Orders can have duplicate numbers eventually, so this connection is needed. The key on the table is the serial number.

I am updating the new field MYCRTDAT using the following SQL. It appears to work correctly, but only with the FETCH FIRST 1 ROW ONLY statement. I verified that the rows updated with the correct date by quickly looking through them.

When NOT using that statement, it returns with an error that a subselect of a basic predicate has produced more than one row

I think this error makes sense, but I don't understand how VC2.SERIALNUM is 'passed' --- is it as a singular value or all the rows at once? And if it's all the rows at once how does FETCH FIRST not just return the first row every time?

UPDATE SCHEMA.TABLE VC2
SET MYCRTDAT = (
-- Get Date from Associated Table
SELECT
    MO1.CRDT
FROM 
SCHEMA.TABLE VC1
  LEFT JOIN  SCHEMA.M1 MO1 ON
    VC1.ORDER = MO1.ORDER
    AND VC1.ITEM = MO1.ITEM
WHERE VC2.SERIALNUM = VC1.SERIALNUM
FETCH FIRST 1 ROW ONLY
);

IBM DB2 for i - 7.1 (POWER SERIES)

Upvotes: 1

Views: 2830

Answers (2)

user7392562
user7392562

Reputation:

I think better version of this query should be by using MERGE statement

MERGE INTO SCHEMA.TABLE VC2
USING SCHEMA.TABLE VC1 LEFT JOIN  SCHEMA.M1 MO1
...
WHEN MATCHED THEN UPDATE MYCRTDAT = MO1.CRDT
else
ignore

Upvotes: 0

Charles
Charles

Reputation: 23783

You have what's known as a correlated subquery. The VC2.SERIALNUM = VC1.SERIALNUM is why it's correlated.

Conceptually, for every value VC2.serialnum the subquery is run with that value.

Thus, why FETCH FIRST is needed and why it returns the first row for each serailnum.

You can imagine, a correlated subquery often performs very poorly. In some cases, the Db can re-write the query so that it doesn't have to repeatedly access the inner table; particular in the case of an UPDATE of a single column like you have.

You might want to look at the MERGE statement, you should have it at 7.1.

Upvotes: 4

Related Questions