CODEWITHSUNDEEP
sqlsql-updateaggregateself-join
Jon Wilson
Jon Wilson

Reputation: 776

Update query with aggregate self-join

I am using a self-join to get year-to-date totals. This works:

SELECT tc.EmployeeID, tc.TimeCardNum, tc.Tax, SUM(inr.Tax) AS YTDTax
FROM TimeCards tc
JOIN (
        SELECT EmployeeID, TimeCardNum, Tax
        FROM TimeCards  
) AS inr
ON inr.EmployeeID = tc.EmployeeID
AND inr.TimeCardNum <= tc.TimeCardNum
GROUP BY tc.EmployeeID, tc.TimeCardNum, tc.Tax
ORDER BY tc.EmployeeID, tc.TimeCardNum

which gives me a running total column that resets for each employee.

Now I want to turn it into an UPDATE query to put that data into a new column. How is this done?

Upvotes: 1

Views: 2527

Answers (1)

garnertb
garnertb

Reputation: 9594

Have you tried:

UPDATE a
SET a.YTDTax=b.YTDTax
FROM TimeCards a
INNER JOIN (
    SELECT tc.EmployeeID, tc.TimeCardNum, tc.Tax, SUM(inr.Tax) AS YTDTax
    FROM TimeCards tc
    JOIN (
            SELECT EmployeeID, TimeCardNum, Tax
            FROM TimeCards  
    ) AS inr
    ON inr.EmployeeID = tc.EmployeeID
    AND inr.TimeCardNum <= tc.TimeCardNum
    GROUP BY tc.EmployeeID, tc.TimeCardNum, tc.Tax
) b on a.EmployeeID=b.EmployeeID
AND a.TimeCardNum = b.TimeCardNum

Run in a transaction to ensure it produces the desired output.

Upvotes: 2

Related Questions