When performing an update query (the following is just an example; any update query could be used) such as:
update t1
inner join t2 on t1.id=t2.id
set t1.name="foo" where t2.name="bar";
Query OK, 324 rows affected (1.82 sec)
how do you see which rows have been affected (the 324 rows affected in the response)? I tried converting the expression to a select, such as
select * from t1
inner join t2 on t1.id=t2.id
where t1.name="foo";
but this also returns the rows that were already name="foo" before the update, too.
Conceptually, I would like to do something like
select * from rows_affected;
but of course this does not work. Is there a method that will allow the inspection/selection of rows affected by an update query? Or is the only solution to do the select before the update to see which rows will be affected?
Best Answer
This sounds like a job for SELECT FOR UPDATE. Please see MySQL Docs on this
I have discussed this over the years
Aug 08, 2011: Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?Jan 02, 2012: LOCK IN SHARE MODEMar 18, 2012: select for update gives error on indexed columnMay 09, 2012: Transaction Lock Timeouts When Updating a RowMay 13, 2012: Cannot update certain rows in innodb tablesAug 10, 2012; Similar function NOWAIT in MySQLFeb 12, 2014: row locking within ACID transaction innodb