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
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
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.