1

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?

1
  • Check out RETURNING in MariaDB.
    – Rick James
    Aug 13 '20 at 2:52
1

This sounds like a job for SELECT FOR UPDATE. Please see MySQL Docs on this

I have discussed this over the years

0

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

3
  • I don't think that is true, from what I remember of MySQL it won't perform an update if the value already matches what the update statement is changing, So from the query, you are using it is possible it could return more than 324 records if some of those records already had a name of "foo". This also isn't counting for the possibility of more records being inserted between the update and select.
    – Joe W
    Aug 12 '20 at 14:37
  • i added more explanation, about your concern with the not updated rows. Your second concern is wrong, the question was can we detect t1 rows , that are effected.
    – nbk
    Aug 12 '20 at 14:53
  • The question was about returning rows that where updated not about returning all rows that match a condition. That is why they asked about getting just the rows that where updated and not just a general select of rows that could have been updated..
    – Joe W
    Aug 12 '20 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.