How to update only one record in DB2?

Question

In DB2, I need to do SELECT FROM UPDATE to install the update + select in one transaction.
But I need to update only one record per transaction.

Familiar with the MySQL LIMIT UPDATE LIMIT

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

I searched for something similar in the DB2 UPDATE link , but to no avail.

How can you achieve something similar 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 yet been provided.

Answer 1

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

To limit the number of rows that have been modified using UPDATE :

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

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

---

INSERT limited number of newlines:

 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; 

<h / "> So DB2 supports SELECT from an UPDATE , INSERT or DELETE :

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

SELECT will only return data from modified rows.

Answer 2

You have two options. As Unnamed Horse noted, you can use the table's primary key to ensure that one row is updated at a time.

An alternative, if you use a programming language and have control over the cursors, is to use a cursor with the "FOR UPDATE" option (although this may not be necessary; IIRC, the "FOR UPDATE" cursors by default when the base SELECT means it can be) and then use the UPDATE statement with WHERE CURRENT OF <cursor-name> in the UPDATE statement. This will update the single line currently addressed by the cursor. The syntax information depends on the language you are using, but raw SQL looks like this:

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

If you cannot write DECLARE in your host language, you need to do a manual beating to find an equivalent mechanism.