Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

select the rows affected by an update

Tags:

sql

mysql

atomic

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?)

like image 938
Will Avatar asked Jul 02 '10 07:07

Will


2 Answers

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
like image 136
Naktibalda Avatar answered Oct 26 '22 01:10

Naktibalda


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.

like image 45
Mike Avatar answered Oct 26 '22 01:10

Mike