In DB2, I need to do a
SELECT FROM UPDATE
But I need to make sure to update only one record per transaction.
Familiar with the
places a limit on the number of rows that can be updated
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 ( SELECT t.column1 FROM someschema.sometable t WHERE ... FETCH FIRST ROW ONLY ) SET column1 = 'newvalue';
UPDATE statement never sees the base table, just the expression that filters it, so you can control which rows are updated.
INSERTa 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;
SELECT column1 FROM NEW TABLE ( UPDATE ( SELECT column1 FROM someschema.sometable WHERE ... FETCH FIRST ROW ONLY ) SET column1 = 'newvalue' ) AS x;
SELECT will return data from only the modified rows.