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, Mar 19 '14 at 19:40
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Conrad Frix, Mar 19 '14 at 22:52

score 7 · Answer 1 · answered Mar 19 '14 at 19:39

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 '14 at 19:39

D Stanley

149,601
11
178
240

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... – Dave Mason Mar 19 '14 at 19:43
Actually, I am doing it in a stored procedure, but I stopped at this point currently. – OJazem Mar 19 '14 at 19:45
Doing what? Calculating the running balance? Your question indicates that it's a field in the physical table. – D Stanley Mar 19 '14 at 19:46
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. – OJazem Mar 19 '14 at 19:52
5

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. – D Stanley Mar 19 '14 at 19:54

Update multiple rows with different values in a single query

1 Answers1