Update multiple rows with different values in a single query

Question

I have a General Ledger Table in a database (SQL Server 2005), With columns (ID, Insertion_Date, Invoice_No, Debit, Credit and Balance).

When a user add some entries, he will be given to fill the invoice no. and amount(Debit) so Balance will automatically be added as Balance = Balance - Amount. Anyway, after having multiple rows for the same invoice, the user decided to edit the amount for a previous row. After editing the amount, the balance of the same row and the other rows should change automatically. (As: the edited row will take the new amount like: Balance = Balance - newamount, and the other rows will take the new balance and sub. with their current amount).

Example: Table GL

ID   Date       Invoice_No    Debit    Credit    Balance
---------------------------------------------------------
1   19/3/2014    123456        0        400        400
2   19/3/2014    123456       100         0        300
3   20/3/2014    123456        50         0        250
4   21/3/2014    123456       100         0        150
5   22/3/2014    123456        50         0        100

After Editing the row with ID 2 and making the change on the Debit Column (instead of 100 making it 50) only, The other rows will be effected so the result should look like this.

ID   Date       Invoice_No    Debit    Credit    Balance
---------------------------------------------------------
1   19/3/2014    123456        0        400        400
2   19/3/2014    123456        50         0        350
3   20/3/2014    123456        50         0        300
4   21/3/2014    123456       100         0        200
5   22/3/2014    123456        50         0        150

Any Idea how can I manage this with a single query. Am new at SQL and need help. Please ask for more Info. if needed.

consider to use a trigger for this. When you update some amount, the trigger update every other row — fnightangel
– fnightangel, Commented Mar 19, 2014 at 19:40
possible duplicate of Calculate a Running Total in SqlServer — Conrad Frix
– Conrad Frix, Commented Mar 19, 2014 at 22:52

D Stanley · Accepted Answer · 2014-03-19 19:39:08Z

7

I would STRONGLY advise you NOT to keep a "running total" as a field in the transaction table - calculate it in a view, stored procedure, or in the consuming application. Otherwise a change to one record will cascade to potentially every other record in the table.

answered Mar 19, 2014 at 19:39

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fuck StackOverflow Over a year ago

Excellent advice. The OP might also consider making Debit and Credit into one column containing positive/negative values as appropriate. This might be a bit off topic, though...

OJazem Over a year ago

Actually, I am doing it in a stored procedure, but I stopped at this point currently.

D Stanley Over a year ago

Doing what? Calculating the running balance? Your question indicates that it's a field in the physical table.

OJazem Over a year ago

I did the stored procedure for a simple update process not the running balance. Planning to add this operation to the stored procedure if possible.

D Stanley Over a year ago

Putting the update in a sproc doesn't make it any better - you're still cascading one update to multiple records. DON'T store the running balance - CALCULATE it when you GET the data.

Collectives™ on Stack Overflow

Update multiple rows with different values in a single query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related