Solved

# UPDATE Row using values from prior row

Posted on 2008-09-30
729 Views
Hello,
I have this:

UPDATE MONEY SET Balance = Credit - Debit + "X"

Ok, that "X" is supposed to be the prior row. I need to calculate the Balance, which is the result of the previous balance + the result of credit + debit. The thing is by the nature of our business we need to alter some old rows and recalculate the balance. The update works that way? How to get the value for a column in the previous row?

thanks.
0
Question by:starusa
• 4
• 3

LVL 59

Accepted Solution

Kevin Cross earned 250 total points
ID: 22610200
Do you have an incrementing primary key (PK) or some other column that indicates a row is older than the one you are on?  If so, you could do something like this:

``````UPDATE MONEY

SET Balance = Credit - Debit +

IsNull((SELECT b.balance FROM MONEY b WHERE b.pkColumn IN (SELECT max(pkColumn) FROM MONEY c WHERE c.pkColumn < MONEY.pkColumn)), 0.00)
``````
0

LVL 39

Assisted Solution

BrandonGalderisi earned 250 total points
ID: 22610767
I would POSSIBLY suggest not storing the balance then, if values are subject to change.  But that can be figured out

Or, you will have to run this frequently:

;with CurrentPast as
(select AccountNo, TransDate, Credit, Debit, cast(Null as money) as PreviousCredit, cast(Null as money) as PreviousDebit, row_number() over (partition by AccountNo order by TransDate) rn
from Money
union all
select mpAccountNo, mp.TransDate, mp.Credit, mp.Debit, row_number() over (partition by AccountNo order by TransDate) rn
from Money m
join CurrentPast cp
on m.accountNo = cp.AccountNo
and m.rn-1 = cp.rn)
select * from CurrentPast

^^ This will give you the each transaction as pairs. ^^

;with CurrentPast as
(select AccountNo, TransDate, Credit, Debit, cast(credit-debit as money) as CurrentChange, cast(0 as money) as PreviousBalance, row_number() over (partition by AccountNo order by TransDate) rn
from Money
union all
select mp.AccountNo, mp.TransDate, mp.Credit, mp.Debit, row_number() over (partition by AccountNo order by TransDate) rn
from Money m
join CurrentPast cp
on m.accountNo = cp.AccountNo
and m.rn-1 = cp.rn)

Now this is some sample code that shows you how to show a Running balance without ever changing it.  And it allows you to see a balance as of a point in time:

``````set nocount on

create table #tabMoney

(accountNo     int

,transDate     datetime

,Credit        money

,Debit         money

)

insert into #TabMoney values(1,'1/1/2007',0,20)

insert into #TabMoney values(1,'1/2/2007',20,0)

insert into #TabMoney values(1,'1/3/2007',0,15)

insert into #TabMoney values(1,'1/4/2007',10,0)

insert into #TabMoney values(2,'1/1/2007',0,100)

insert into #TabMoney values(2,'2/2/2007',0,50)

insert into #TabMoney values(2,'3/3/2007',160,0)

insert into #TabMoney values(2,'4/1/2007',0,10)

go

;with CurrentPast as

(

select t.* from

(select AccountNo

,TransDate

,Credit

,Debit

,credit-debit as CurrentChange

,credit-debit as CurrentBalance

,row_number() over (partition by AccountNo order by TransDate) as rn

from #tabMoney) t

where t.rn=1

union all

select m.AccountNo, m.TransDate, m.Credit, m.Debit, m.CreditDebit, cp.CurrentChange+CreditDebit, m.rn

from

(select AccountNo

,TransDate

,Credit

,Debit

,credit-debit CreditDebit

,row_number() over (partition by AccountNo order by TransDate) rn

from #tabMoney) m

inner join CurrentPast cp

on  m.accountNo = cp.AccountNo

and m.rn-1 = cp.rn

)

select * from CurrentPast

Order by AccountNo,rn

go

drop table #tabMoney
``````
0

LVL 39

Expert Comment

ID: 22610769
There are some assumptions based upon field names and structures.  But it can be easily adapted if you table structure is nothing like this.
0

LVL 59

Expert Comment

ID: 22610891
I would agree with Brandon on not storing if you don't have to and if you want to use a common table expression and do have SQL 2005 you can modify the first query I gave you to do this:
``````WITH moneyCTE AS (

SELECT *,

row_number() over (ORDER BY transdate) AS rn

FROM Money

)

UPDATE a

SET a.Balance = a.Credit - a.Debit + IsNull(b.balance, 0.00)

FROM moneyCTE a LEFT JOIN moneyCTE b

ON a.rn-1 = b.rn
``````
0

LVL 59

Expert Comment

ID: 22610901
As Brandon, is saying here is he same query as a select instead of an update.
``````WITH moneyCTE AS (

SELECT *,

row_number() over (ORDER BY transdate) AS rn

FROM Money

)

SELECT *, -- you can list out columns here

(a.Credit - a.Debit + IsNull(b.balance, 0.00)) AS newBalance

FROM moneyCTE a LEFT JOIN moneyCTE b

ON a.rn-1 = b.rn
``````
0

LVL 39

Expert Comment

ID: 22610909
Same basic principal except I contained all of logic within the CTE to simplify the select.  6 & 1/2 dozen :)
0

LVL 59

Expert Comment

ID: 22610948
True.  Was just extending my query and at first glance your query looked much different with the union all statement, but I see now where you were going with that.

Regards,
Kev
0

## Featured Post

### Suggested Solutions

Sql Query Lookup based on range 2 29
Access Query 4 34
configure service broker on all databases 2 68
another query question 7 25
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…