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?

Answer 1

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

Answer 2

I think you're just looking for the ROW_COUNT() function:

SELECT ROW_COUNT() as `affected_rows`;

It returns the number of rows affected by the previous query, so run it immediately after your CREATE, UPDATE or DELETE to get the desired total.

Result:

+---------------+
| affected_rows |
+---------------+
|             3 |
+---------------+

Answer 3

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

1. SELECT the ids where already the t1.name is 'foo'
2. UPDATE your table
3. 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;

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

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