I was writing an update script where I needed to do a second update only if the first update actually affected the target row.
I added additional filters in the first update where clause so that it would only update on certain consitions, then I capture the ROW_COUNT() and add that to filter the where clause on the second update. This works great so that I don't have to run additional select queries and conditionals, or more complicated SQL statements, to get my desired results.
set @rc = 0;
set @rc1 = 0;
set @rc2 = 0;
set @rc3 = 0;
set @rcx = 0;
drop table if exists testpeople;
create table testpeople (contactid INT(3) not null default 0, firstname varchar(100)) ENGINE=InnoDB;
insert into testpeople (contactid, firstname) values (234, 'Joe');
update testpeople set firstname = 'Joe99' where contactid = 234; ## no effect
update testpeople set firstname = 'Joe1' where contactid = 234;
set @rc = ROW_COUNT(); ## = 1
set @rc1 = @rc; ## = 1
#set @rc = ROW_COUNT(); #### If we called this again here it would clear it and prevent the next update from completing
set @rcx = @rc; ## = 1 or -1 if the line above is commented out
update testpeople set firstname = 'Joe2' where contactid = 234 and @rc = 1; #### Could be @rc > 0 or whatever you expected from previous update
set @rc = ROW_COUNT();
set @rc2 = @rc;
select @rc as rc, @rc1 as rc1, @rc2 as rc2, @rcx as rcx, (select firstname from testpeople where contactid = 234) as endname;
drop table if exists testpeople;
set @rc1 = 0;
set @rc2 = 0;
set @rc3 = 0;
set @rcx = 0;
drop table if exists testpeople;
create table testpeople (contactid INT(3) not null default 0, firstname varchar(100)) ENGINE=InnoDB;
insert into testpeople (contactid, firstname) values (234, 'Joe');
update testpeople set firstname = 'Joe99' where contactid = 234; ## no effect
update testpeople set firstname = 'Joe1' where contactid = 234;
set @rc = ROW_COUNT(); ## = 1
set @rc1 = @rc; ## = 1
#set @rc = ROW_COUNT(); #### If we called this again here it would clear it and prevent the next update from completing
set @rcx = @rc; ## = 1 or -1 if the line above is commented out
update testpeople set firstname = 'Joe2' where contactid = 234 and @rc = 1; #### Could be @rc > 0 or whatever you expected from previous update
set @rc = ROW_COUNT();
set @rc2 = @rc;
select @rc as rc, @rc1 as rc1, @rc2 as rc2, @rcx as rcx, (select firstname from testpeople where contactid = 234) as endname;
drop table if exists testpeople;
That's a wrap!