Legacy and Other...

T-SQL UPDATE Statement

Please support our Legacy and Other Languages advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Dec 2007

Posts: 31

Reputation: AaronASterling is an unknown quantity at this point

Solved Threads: 5

AaronASterling AaronASterling is offline

Offline

Light Poster

T-SQL UPDATE Statement

Feb 24th, 2008

Hello,

I am new to SQL. I assume that this is very simple. I have had trouble understanding the
relevant example in the BOL as my installation (Express edition) does not seem to have the adventureworks database that is so ubiquitous in the examples.

Say I have two tables, Test1 and Test2 Defined as

Help with Code Tags

(Toggle Plain Text)

CREATE TABLE Test1
(
    Test1ID     int     IDENTITY  NOT NULL PRIMARY INDEX,
    Value        int                     NOT NULL
)

CREATE TABLE Test2
(
    Test2ID     int     IDENTITY NOT NULL PRIMARY INDEX,
    Test1ID     int                    NOT NULL FOREIGN KEY REFERENCES Test1(Test1ID),
    Value        int                    NOT NULL
)

What I want to do is have it so that Value entry in a row Test1 is equal to the sum of Value in all rows of Test2 that have that same value for Test1ID. Consider if Test1 was a table of products, Test1.Value was the total number of the product Test1.Test1ID ordered and Test2 was a table of individual orders for the product Test2.Test1ID of quantity Test2.Value?

I am trying to use the update statement

Help with Code Tags

(Toggle Plain Text)

UPDATE Test1
SET Value = SELECT SUM(Value) 
                   FROM Test2 t2
                   WHERE t2.Test1ID =  XXXXXX

I want XXXXXX to represent the value of Test1ID from the same row as the Value that is being set. I suspect that this is the wrong way to go about things.

Thanks for any help.

•

Join Date: Dec 2007

Posts: 31

Reputation: AaronASterling is an unknown quantity at this point

Solved Threads: 5

AaronASterling AaronASterling is offline

Offline

Light Poster

Re: T-SQL UPDATE Statement

Feb 24th, 2008

The solution that I found is

Help with Code Tags

(Toggle Plain Text)

UPDATE Test1
SET Value = Value + t2vs
FROM Test1 t1
INNER JOIN (SELECT Value, t2vs = SUM(Value)
                   FROM Test2 t2
                   GROUP BY t2.Test1ID) m
ON t1.Test1ID = m.Test1ID

Can anyone think of a better one? There is nothing wrong with this one that I know of in particular but if there is a faster way to do it, i would of course be interested.

Thank you,
Aaron