In DB2, I have to perform a [cde], to place an update + choose in one transaction.
But I have to make certain to update just one record per transaction.
Acquainted with the
SELECT FROM UPDATE clause from MySQL's [cde] option
places a restriction on the amount of rows that may be up-to-date
I sought out such like in DB2's [cde] reference but with no success.
Just how can such like be accomplished in DB2?
Edit: During my scenario, I must deliver 1000 online coupons upon request. I simply need to choose (any)one which is not given yet.
You've two options. As noted by A Horse With No Name, you should use the main key on the table to make sure that certain row is up-to-date at any given time.
The choice, if you are utilizing a programming language and also have treatments for cursors, is by using a cursor using the 'FOR UPDATE' option (though that might be most likely optional IIRC, cursors are 'FOR UPDATE' automatically once the underlying Choose means it may be), after which make use of an UPDATE statement using the
LIMIT within the UPDATE statement. This can update the main one row presently addressed through the cursor. The particulars from the syntax vary using the language you are using, however the raw SQL appears like:
If you cannot write DECLARE inside your host language, you need to do manual bashing to obtain the equivalent mechanism.
The question uses some ambiguous terminology that causes it to be unclear what must be accomplished. Fortunately, DB2 offers robust support for a number of SQL designs.
To limit the amount of rows which are modified by an update:
The update statement never sees the bottom table, only the expression that filters it, so that you can control which rows are up-to-date.
To place a restricted quantity of new rows:
WHERE CURRENT OF <cursor-name>
This is the way DB2 supports Choose from an update, place, or remove statement:
DECLARE CURSOR cursor_name FOR SELECT * FROM SomeTable WHERE PKCol1 = ? AND PKCol2 = ? FOR UPDATE; UPDATE SomeTable SET ... WHERE CURRENT OF cursor_name;
The Choose returns data from just the modified rows.