AnswerBun.com

How to select the rows affected by an update

Database Administrators Asked by enharmonic on October 27, 2020

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?

2 Answers

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

I have discussed this over the years

Answered by RolandoMySQLDBA on October 27, 2020

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

Answered by nbk on October 27, 2020

Add your own answers!

Related Questions

SqlPackage does not pick up variables from profile

3  Asked on December 24, 2021 by thx-1138

 

This simple mysql query is taking 2-3 seconds

2  Asked on December 24, 2021 by mikkergp

     

Powershell Invoke-Sqlcmd Replacing £ with?

1  Asked on December 24, 2021 by dennis-finke

   

Upgrade Postgres 9.6 to 12

0  Asked on December 24, 2021 by doug-p

 

Stored procedure for adding cascade

1  Asked on December 22, 2021 by user73639

     

MySQL Optimization Guidance Needed

3  Asked on December 22, 2021 by enyceexdanny

   

Sybase IQ cache database result

1  Asked on December 21, 2021 by daria

   

Help Outputing MySQL General Log to Named Pipe

2  Asked on December 21, 2021 by widdlewabbit

 

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir