Marius Butuc Marius Butuc - 5 months ago 21
MySQL Question

How to UPDATE just one record in DB2?

In DB2, I need to do a

, to put an update + select in a single transaction.

But I need to make sure to update only one record per transaction.

Familiar with the
LIMIT
clause from MySQL's
UPDATE
option


places a limit on the number of rows that can be updated


I looked for something similar in DB2's
UPDATE
reference
but without success.

How can something similar be achieved in DB2?




Edit: In my scenario, I have to deliver 1000 coupon codes upon request. I just need to select (any)one that has not been given yet.

Answer

The question uses some ambiguous terminology that makes it unclear what needs to be accomplished. Fortunately, DB2 offers robust support for a variety of SQL patterns.

To limit the number of rows that are modified by an UPDATE:

UPDATE 
( SELECT t.column1 FROM someschema.sometable t WHERE ... FETCH FIRST ROW ONLY
) 
SET column1 = 'newvalue';

The UPDATE statement never sees the base table, just the expression that filters it, so you can control which rows are updated.


To INSERT a limited number of new rows:

INSERT INTO mktg.offeredcoupons( cust_id, coupon_id, offered_on, expires_on )
SELECT c.cust_id, 1234, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 30 DAYS 
FROM mktg.customers c 
LEFT OUTER JOIN mktg.offered_coupons o
ON o.cust_id = c.cust_id
WHERE ....
AND o.cust_id IS NULL
FETCH FIRST 1000 ROWS ONLY;


This is how DB2 supports SELECT from an UPDATE, INSERT, or DELETE statement:

SELECT column1 FROM NEW TABLE (
    UPDATE ( SELECT column1 FROM someschema.sometable 
             WHERE ... FETCH FIRST ROW ONLY
    ) 
    SET column1 = 'newvalue'
) AS x;

The SELECT will return data from only the modified rows.