MS SQL Update one row from many rows

Posted on 2008-11-06
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

Question by:HBHoffman
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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

    LVL 39

    Accepted Solution

    Because youre result is evaluated on a line by line bases.  Run the select. and you'll see.  try this:


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


    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

    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

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    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 ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Here's a very brief overview of the methods PRTG Network Monitor ( 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…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now