MS SQL Update one row from many rows

Run the attached script.

Why is field val_1 in #table1 = 100.00? I'm expecting 600.00. Any suggestions? Thanks
CREATE TABLE [dbo].[#table1](
	[_key] [int],
	[val_1] [money])
 
CREATE TABLE [dbo].[#table2](
	[_key] [int],
	[val_2] [money])
 
INSERT INTO #table1 VALUES (1,0)
INSERT INTO #table2 VALUES (1,100.00)
INSERT INTO #table2 VALUES (1,200.00)
INSERT INTO #table2 VALUES (1,300.00)
 
UPDATE #table1
	SET val_1 = IsNull(val_1,0) + IsNull(val_2,0) 
FROM #table2  
WHERE #table2._key = #table1._key
 
SELECT * FROM #table1
 
DROP TABLE #table1
DROP TABLE #table2

Open in new window

LVL 8
Bob HoffmanDeveloperAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Because youre result is evaluated on a line by line bases.  Run the select. and you'll see.  try this:


Swap:

UPDATE #table1
        SET val_1 = IsNull(val_1,0) + IsNull(val_2,0)
FROM #table2  
WHERE #table2._key = #table1._key

With:


UPDATE t1
 
        SET val_1 = IsNull(val_1,0) + IsNull(val_2,0) 
FROM #table1 t1
join (select _key,sum(val_2) from #table2 group by _key) t2
on t1._key = t2._key

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
because you don't do the sum:
UPDATE #table1
   SET val_1 = IsNull(val_1,0) + (SELECT SUM(IsNull(val_2,0) FROM #table2 WHERE #table2._key = #table1._key )

Open in new window

0
 
Bob HoffmanDeveloperAuthor Commented:
Both suggestions worked. Because my actual UPDATE has 36 fields that need to be updated BrandonGalderisi solution seemed to be the better way to go. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.