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's gravatar image

gchq
281161930
accept rate: 33%


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

permanent link

answered 05 Jun, 01:46

Christian%20Hamers's gravatar image

Christian Ha...
53191226
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 <join-condition>;
(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) Sako
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×168

question asked: 04 Jun, 18:47

question was seen: 89 times

last updated: 05 Jun, 18:22