?
Solved

MS SQL Update one row from many rows

Posted on 2008-11-06
3
Medium Priority
?
757 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:Bob Hoffman
3 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 22900537
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1600 total points
ID: 22900547
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
 
LVL 8

Author Closing Comment

by:Bob Hoffman
ID: 31514172
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question