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