UPDATE records WHERE SELECT involves aggregate function

[former_SQLA_member1694879]

Assuming that I return records using this SELECT Statement

SELECT SUM(Debit) as 'Debit_Total', SUM(Credit) as 'Credit_Total', Customer_ID, Sum(Debit)- Sum(Credit) as 'Balance' FROM A_Sales_Ledger GROUP BY Customer_ID HAVING Balance <= 0 ORDER BY Balance

Now if I want to update a different column based on those rows, how do I achieve that? Somehow the brain has left the building and I can't figure it out 🙂

[Chris26]

I haven't tested this, but something like this should work

UPDATE A_Sales_Ledger SET <myOtherColumn> = a.Balance FROM A_Sales_Ledger INNER JOIN (SELECT SUM(Debit) as 'Debit_Total', SUM(Credit) as 'Credit_Total', Customer_ID, Sum(Debit)- Sum(Credit) as 'Balance' FROM A_Sales_Ledger GROUP BY Customer_ID HAVING Balance <= 0) AS a ON a.Customer_ID = A_Sales_Ledger.Customer_ID

[VolkerBarth]

Just to add:

The basic pattern here for a table A and a query Q that involves a join to A is to turn the query into a derived query and join that with A:

UPDATE A
SET A.x = DT.y
FROM A INNER JOIN (Q) DT ON <join-condition>;

[justin_willey]

I find it all too easy to forget just how much can be done with derived queries! Had an embarrassing example recently where rewriting a loop as a derived summary query reduced execution time by 98%.

[Baron]

I assume that you want to update some column let say column name is iscustomernegative in table A_Sales_Ledger.

For example: update A_Sales_Ledger set iscustomernegative = 'YES';

Then I would write my statement as follows:

update A_Sales_Ledger set iscustomernegative = 'YES' where Customer_ID in

(select Customer_ID from (

SELECT SUM(Debit) as 'Debit_Total', SUM(Credit) as 'Credit_Total', Customer_ID, Sum(Debit)- Sum(Credit) as 'Balance' FROM A_Sales_Ledger GROUP BY Customer_ID HAVING Balance <= 0 ORDER BY Balance

) TRANS_TABLE)