munale munale - 11 days ago 4
SQL Question

How can i get UPDATE statement to execute twice on the same row in SQL Server

Im trying to update a table in SQL. Specifically trying to update a numeric field by subtracting a numeric value in another table that I am linking by a primary key. For example update this table

ID NAME     BALANCE    CURRSTATUS

1   FRED      45               OWED

2   JAMES    21               OWED

3   JOHN      0                 PAID

4   BETH      33               OWED

5   HARRY   5                REFUND

6   WILLIAM 555            COLLECT



by applying the rows of this table

ID   AMOUNT
6  
500.00
6  
55.00



so my expected result would the the same as the first table except the balance for William would be 0(555-500-55 = 0)




i am using a sql statement like this:

UPDATE TABLE1
SET BALANCE = BALANCE - TABLE2.AMOUNT
FROM TABLE2
LEFT OUTER JOIN TABLE1 ON TABLE1.ID = TABLE2.ID


this works for the first line but doesnt execute the 2nd line in table2. so my end result is always 55 instead of 0. FWIW i only have this problem when the two lines in the second table are for the same person. for example if i had a line for William and a line for John, they would both behave as expected.

am i missing something? or is it just how UPDATE works?

Answer

Pre-aggregate:

UPDATE t1
    SET BALANCE = t1.BALANCE - t2.sum_amount
FROM Table1 t1 JOIN
     (SELECT ID, SUM(Amount) as sum_amount
      FROM TABLE2
      GROUP BY ID
     ) t2
     ON t2.ID = t1.ID ;
Comments