1

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?

Justin
  • 9,634
  • 6
  • 35
  • 47
J Harri
  • 407
  • 2
  • 7
  • 15
  • 1
    This is called a transaction. You can read the documentation here . . . http://dev.mysql.com/doc/refman/5.0/en/commit.html. – Gordon Linoff Mar 22 '13 at 14:50
  • possible duplicate of [OUTPUT Clause in MySQL](http://stackoverflow.com/questions/5817414/output-clause-in-mysql) – lc. Mar 22 '13 at 14:50
  • I agree my question is a dup, I missed this question in my search. And thanks to Justin for making the statement more readable. – J Harri Mar 22 '13 at 14:58

2 Answers2

1

Do something like:

START TRANSACTION;
UPDATE ....
SELECT ....
COMMIT;
Brad
  • 11,262
  • 8
  • 55
  • 74
  • Although functional, this might not be the best solution because it may degrade performance greatly (locking the whole table), unless if this is not supposed to be an operation to happen often. – Fabio Apr 18 '20 at 18:53
1

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;
ahuigo
  • 2,929
  • 2
  • 25
  • 45
  • 1
    Is this atomic? Disregarding isolation mode for a second, it seems to me like a different connection could easily set `status` back to `0` in between your update and select. Am I wrong? Does last_insert_id() perform some kind of magic I'm unaware of? – Steen Schütt Nov 10 '22 at 09:56