Tuesday, November 4, 2014

Cool MySQL for an update conditional upon a previous update within the same query block.

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.

A mock situation is outlined below. I set additional variables only to capture the state of the row_count at various places during the rest.

Keep in mind that row_count does not accumulate on multiple update statements.


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;



That's a wrap!