select the rows affected by an update

Question

How can you get the exact rows affected by an SQL UPDATE statement in MySQL?

I have many clients on many computers that can be updating rows in the same table based on WHERE clauses at any time, and each client needs to do something in another system for each row that they affect, so getting the list of affected items must be accurate and not vulnerable to race-conditions.

Some databases support UPDATE ... OUTPUT UPDATED.id WHERE ... e.g. SQL Server.

How can you do this atomic UPDATE/SELECT in MySQL?

(I've seen suggestions of doing the SELECT first and then using the IDs as an IN clause in the UPDATE. But another client might run the same SELECT and retrieve the same rows whilst the first client is queuing its UPDATE etc?)

Answer 1

This is what I have used before, and there's an instance of someone else using it here on Stackoverflow:

UPDATE my_table SET
    id = (SELECT @id := id),
    <column> = 'value'        
    WHERE <condition>;

@id will contain the ID of updated row, or NULL if no row was updated. This would only work for single-row updates.

Answer 2

Use table locking or transactions (if supported by storage engine) to prevent race conditions.

LOCK TABLES tablename;
SELECT * FROM tablename WHERE x.
do something else
UPDATE tablename SET y WHERE x.
UNLOCK TABLES