CODEWITHSUNDEEP
mysqldatabasetransactionsdb2
Marius Butuc
Marius Butuc

Reputation: 18250

How to UPDATE just one record in DB2?

In DB2, I need to do a SELECT FROM UPDATE, 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.

Upvotes: 5

Views: 10715

Answers (2)

Fred Sobotka
Fred Sobotka

Reputation: 5332

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.

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 753735

You have two options. As noted by A Horse With No Name, you can use the primary key of the table to ensure that one row is updated at a time.

The alternative, if you're using a programming language and have control over cursors, is to use a cursor with the 'FOR UPDATE' option (though that may be probably optional; IIRC, cursors are 'FOR UPDATE' by default when the underlying SELECT means it can be), and then use an UPDATE statement with the WHERE CURRENT OF <cursor-name> in the UPDATE statement. This will update the one row currently addressed by the cursor. The details of the syntax vary with the language you're using, but the raw SQL looks like:

DECLARE CURSOR cursor_name FOR
    SELECT *
      FROM SomeTable
     WHERE PKCol1 = ? AND PKCol2 = ?
       FOR UPDATE;

UPDATE SomeTable
   SET ...
 WHERE CURRENT OF cursor_name;

If you can't write DECLARE in your host language, you have to do manual bashing to find the equivalent mechanism.

Upvotes: 2

Related Questions