CODEWITHSUNDEEP
mysqlselect
J Harri
J Harri

Reputation: 407

Does MySQL Support Atomic Select and Update Together

I'm looking for something like the following in MySQL:

SELECT Field1 
FROM MyTable 
AFTER UPDATE 
SET Field2 = 'myvalueX' 
WHERE Field3 = 'myvalueY';

The above hypothetical statement returns the values for Field1 for each row that was affected by the update part of the statement.

Does such a statement exist in MySQL?

Upvotes: 1

Views: 2038

Answers (2)

ahuigo
ahuigo

Reputation: 3331

If you update one row once only, use last_insert_id() to implement Atomic Select and Update Together :

update <table> set status=1,id=last_insert_id(id) where status=0 limit 1;
select * from <table> where id=last_insert_id() limit 1;

Upvotes: 1

Brad
Brad

Reputation: 11515

Do something like:

START TRANSACTION;
UPDATE ....
SELECT ....
COMMIT;

Upvotes: 1

Related Questions