Update with join and unexpected output

Recently one of my colleagues was writing a trigger, and in that, he’s having Update Query with join, syntax, and everything was right, but he’s not getting the expected output. To understand the situation, let’s take an example.

Create two temporary tables:

CREATE TABLE #tmpTable1
( gid smallint,
  gBal smallint
)
CREATE TABLE #tmpTable2
( tid smallint,
  tBal smallint
)

And Inset the following values in both tables.

insert into #tmpTable1(gid,gBal) values(1, 200), (2,300)
insert into #tmpTable2(tid,tBal) values(1, 300), (2,400),(1, 600), (2,700)

Now both the table contains inserted values:

The update query with a join that was not giving the expected output:

update #tmpTable1 set gBal = gBal +  tbal
from #tmpTable1 inner join #tmpTable2 
on gid=tid

As you can see, nothing is wrong with the query, but it is updating it with the first two values of #tmpTable2, and hence the output will be as shown below, but the outcome should be 1100 for the record no 1 and 1400 for the record no 2.

As per the MSDN Documentation:

Single UPDATE statement never updates the same row twice.

Let’s say you have more than one sale for a given item, and you update as shown above; it does not work correctly. It executes without error, but each item is updated with only one sale, regardless of how many sales occurred on that day. In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in the following query:

Update #tmpTable1
SET gBal = gbal +
(SELECT SUM(tbal)
FROM #tmpTable2 AS tmp
WHERE tmp.tid = gid)

This will give us the expected output, which is 

That’s it!!  Enjoy 😉