UPDATE with SUM 1

[DWarren]

I am fairly new to SQL and have not found a solution to the problem below in any of the reference books that I have. Thanks in advance for any help.

Table1 = 1000 rows
Table2 = 8 rows

Ex1

UPDATE Table1 SET Total1=(Total1 + D.Amount1)

FROM Table2 D

WHERE (Table1.ID=D.ID) AND (Table1.ID2=D.ID2)

-- the above affects 8 or fewer rows, but if there is more
-- than one row in Table2 that has a match on Table1,
-- only the first row in Table2
-- updates the column Total1 with Amount1


Ex2

UPDATE Table1 SET Total1=
(SELECT SUM(Amount1) FROM Table2 D
WHERE (Table1.ID=D.ID) AND (Table1.ID2=D.ID2)
GROUP BY D.ID,D.ID2)

-- the above sets Table1.Total1 to the correct sum of
-- records from Table2 BUT all 1000 rows in Table1 are
-- affected setting Amount1 to null
-- on rows that have no link to Table2 (!)

How can I change the SQL to set Amount1 accurately while only affecting the Table1 rows that have a join?

 

[tlbroadbent]

UPDATE Table1 SET Total1=SumAmt
FROM Table1 Inner Join
(SELECT ID, ID2, SUM(Amount1) As SumAmt
FROM Table2 GROUP BY ID, ID2) As d
WHERE Table1.ID=D.ID
AND Table1.ID2=D.ID2

Terry

http://www.defendscouting.com/

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[DWarren]

Thanks, Terry...
Worked perfectly, all I had to do was change WHERE to ON...
David