The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

# UPDATE records WHERE SELECT involves aggregate function

 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 :-) asked 04 Jun, 18:47 gchq 296●16●21●32 accept rate: 33%

 I haven't tested this, but something like this should work ```UPDATE A_Sales_Ledger SET = 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``` answered 05 Jun, 01:46 Christian Ha... 531●9●13●26 accept rate: 33% 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 ;``` (05 Jun, 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, 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: 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) (05 Jun, 18:13) Baron
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×173