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 :-) |
I haven't tested this, but something like this should work
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>;
(05 Jun '20, 02:47)
Volker Barth
1
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%.
(05 Jun '20, 06:17)
Justin Willey
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:
(05 Jun '20, 18:13)
Baron
|