How to select the rows affected by an update

Question

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?

RolandoMySQLDBA · 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 MODE
Mar 18, 2012 : select for update gives error on indexed column
May 09, 2012 : Transaction Lock Timeouts When Updating a Row
May 13, 2012 : Cannot update certain rows in innodb tables
Aug 10, 2012 ; Similar function NOWAIT in MySQL
Feb 12, 2014 : row locking within ACID transaction innodb

nbk · Answer

Many rows can have t1.name="foo".
MySQL will not update that row, if it detects that you already have the value you try to enter
But only  the joined t1 rows that are linked to the condition t2.name="bar" will show up in the following query
select t1.id from t1 
inner join t2 on t1.id=t2.id
where t2.name="bar";

Of course it will not detect , if t1.name has already the value "foo" and so it would show t1.ids that are not affected.
If you need the actual row, without the not affted rows, you must write a stired procedure where you

SELECT the ids where already the t1.name is 'foo'
UPDATE your table
Do the above Query and remove all ids that are in 1.

As an Example

CREATE TABLE t1 (id int auto_increment primary key,name varchar(4));
INSERT INTO t1(name) VALUES('foo'),('foo1'), ('foo'), ('test'), ('test'), ('test'), ('test'), ('test'), ('test'), ('test');
 CREATE TABLE t2 (id int auto_increment primary key,name varchar(4));
INSERT INTO t2(name) VALUES('bar'),('foo1'), ('bar'), ('bar'), ('bar'), ('test'), ('bar'), ('test'), ('test'), ('test');

SELECT * FROm t1;

SELECT * FROM t2;

id | name
-: | :---
 1 | bar 
 2 | foo1
 3 | bar 
 4 | bar 
 5 | bar 
 6 | test
 7 | bar 
 8 | test
 9 | test
10 | test

CREATE TEMPORARY table t1_temp SELECT id FROM t1 WHERE name = 'foo';

update t1 
inner join t2 on t1.id=t2.id
set t1.name="foo" where t2.name="bar";

SELECT * FROM t1;

id | name
-: | :---
 1 | foo 
 2 | foo1
 3 | foo 
 4 | foo 
 5 | foo 
 6 | test
 7 | foo 
 8 | test
 9 | test
10 | test

select t1.id from t1 
inner join t2 on t1.id=t2.id
where t2.name="bar" AND t1.id NOT IN (SELECT id FROM t1_temp);

| id |
| -: |
|  4 |
|  5 |
|  7 |

db<>fiddle here

How to select the rows affected by an update

2 Answers

Add your own answers!

Ask a Question