Solved

# MS SQL Update one row from many rows

Posted on 2008-11-06
746 Views
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
``````
0
Question by:HBHoffman

LVL 142

Assisted Solution

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 )
``````
0

LVL 39

Accepted Solution

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
``````
0

LVL 8

Author Closing Comment

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

### Suggested Solutions

Sql query 12 56
Set the max value for a column 7 21
Delete from table 6 26
How to query LOCK_ESCALATION 4 24
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…