UPDATE with JOIN subtle bug

I have been diagnosing very subtle bug in SQL code which led to unexpected results. It happens under rare circumstances, when you do update with join and you want to increase some number by one. You just write value = value + 1. The thing is, you are willing to increase the value by the number of joined rows. The SQL code kind of expresses your intent. However, what actually happens is, the existing value is read only once. It is updated 3 times, indeed. But with the same value, incremented only by one.

declare @UpdateTarget table(Id bigint, Capacity int); 
insert into @UpdateTarget (Id, Capacity) values (10, 0); -- Lets assume this is our counter of available resources
 
declare @HowManyTimesUpdate table(TargetId bigint);
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
 
-- Now, we are releasing resources. Increase the counter for those 3 reservations
update ut
set ut.Capacity = ut.Capacity + 1
from @UpdateTarget ut join @HowManyTimesUpdate hmt on ut.Id = hmt.TargetId;
 
-- We expect result 3, because 3 resources should be released, but there is only 1
select Capacity from @UpdateTarget;
 
-- Reset and start again
update @UpdateTarget set Capacity = 0;
 
-- Do it right
update ut
set ut.Capacity = ut.Capacity + (select count(1) from @HowManyTimesUpdate where TargetId = [Id])
from @UpdateTarget ut join (select distinct TargetId from @HowManyTimesUpdate) hmt on hmt.[TargetId] = [Id];
 
-- We expect 3, there is 3
select Capacity from @UpdateTarget

Leave a Reply

Your email address will not be published. Required fields are marked *

Protection against spam * Time limit is exhausted. Please reload CAPTCHA.