update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update

Author

Topic

carlossiwaily
Starting Member

24 Posts

Posted - 2006-03-12 : 09:45:57

hello,
I need some help please

I have table A with trid,payin,payout,balance

I need to select & from table A then Update the balance as follow

if payin > 0 then
Update A set balance = paying + balnce(from the previous record)
else if payout > 0 then balance = payout - balnce(from the previous record).
What i have is
trid : payin : payout: balance
12 : 2000 : 0 : 0
13 : 0 : 300 : 0
Waht i realy want is as follow

trid : payin : payout: balance

12 : 2000 : 0 : sum(payin + balnce from previous record
12 : 0 : 300 : sum(payout - balnce from previous record
etc

thanks

carlossiwaily

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-12 : 12:08:19

SQL Server tables have no inherent order, so how do you define what the previous row is?

CODO ERGO SUM

carlossiwaily
Starting Member

24 Posts

Posted - 2006-03-12 : 14:06:42

so what is the solution to this problem

carlossiwaily

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-12 : 14:32:02

quote:
Originally posted by carlossiwaily

so what is the solution to this problem

carlossiwaily

To get an answer, u should ask ur question to be understood by others.
What is ur "Previous" ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-12 : 14:42:07

[code]
update a
set
balance =
case
when a.payin > 0
then a.paying + b.balance
when a.payout > 0
-- Calculation that is probably wrong
-- but is how you specified it
then a.payout - b.balance
end
from
A a
join
A b
on
a.AccountIdentifierThatYouDidNotExplain =
b.AccountIdentifierThatYouDidNotExplain
where
(a.payin > 0 or a.payout > 0 )
and
b.PreviousRowIdentifierThatYouIgnoredMyQuestionAbout =
@RowIdentifierofPreviousRow

[/code]

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-14 : 12:52:07

Thanks, Micheal, I enjoyed that one.

ColleenH1983
Starting Member

7 Posts

Posted - 2007-06-22 : 16:25:20

Hello, I have a question along a similar theme. I am trying to update null records to be equal to the previous record within the same column. By previous, I mean corresponding to the current numeric primary key minus 1. Hopefully that makes sense. Thanks so much. I know this must be simple, but I can't get it to work. I'm not very good with SQL or Access.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 22:13:15

ColleenH1983,

You should post your question in new thread with table DDL, sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources