SELECT @amntMoney = SUM(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END)
FROM   Account

DECLARE @amntMoney money, @MyAncher int
SET @amntMoney = 0
UPDATE A
SET 
@MYancher = a.CLUSTERIDCOLUMN
,@amntMoney = value = @amntMoney + (CASE WHEN operationtype 
= 'deposit' THEN value ELSE -value END) 
FROM 
Account a

quote:

---

Originally posted by Vinnie881

You can update a value using a variable, but keep in mind this method is not supported by sql. The rule of thumb that most people follow to ensure the corerct order is the following.


1. Have a cluster index on the field you want to order by (Should be a ID column though and not a date)
2. Set a anchor variable to that field
3. Don't link any other tables.

DECLARE @amntMoney money, @MyAncher int
SET @amntMoney = 0
UPDATE A
SET 
@MYancher = a.CLUSTERIDCOLUMN
,@amntMoney = value = @amntMoney + (CASE WHEN operationtype 
= 'deposit' THEN value ELSE -value END) 
FROM 
Account a

Keep in mind this SHOULD work, it is not guarenteed that it will in future versions, and this is not a supported method to update rows in sql, since they do not guarentee the order it will process. By keeping a clustered index a anchor and no linking tables, it SHOULD always work, but depending on how important your data is, you might not want this method since it's not supported.

---

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

---

quote:

---

Originally posted by laailalalaa

Vinnie881, visakh16.. thanks for your solution, it seems pretty elaborate for my needs (just a basic school project). however, i don't think that's what i need. as u pointed out, the clustered index should be on an ID field, not a datetime.

i'll provide some test data, case i wasn't clear enough:).
(deposit,100,1-1-2010)
(deposit,50,1-3-2010)
(withdraw,70,2-1-2010)
using update @var=column=newvalue i want to change the table in order to display the current amount of money in the account, after every operation, in chronological order:
(deposit,100,1-1-2010)
(deposit,150,1-3-2010)
(withdraw,80,2-1-2010)
so i need to somehow guarantee the order in which the update takes place (order by date).

thank u

---

SELECT t.operationtype,t1.Amt,t.date
FROM Table t
CROSS APPLY (SELECT SUM(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END) AS Amt
FROM Table
WHERE date < = t.date
)t1

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by laailalalaa

Vinnie881, visakh16.. thanks for your solution, it seems pretty elaborate for my needs (just a basic school project). however, i don't think that's what i need. as u pointed out, the clustered index should be on an ID field, not a datetime.

i'll provide some test data, case i wasn't clear enough:).
(deposit,100,1-1-2010)
(deposit,50,1-3-2010)
(withdraw,70,2-1-2010)
using update @var=column=newvalue i want to change the table in order to display the current amount of money in the account, after every operation, in chronological order:
(deposit,100,1-1-2010)
(deposit,150,1-3-2010)
(withdraw,80,2-1-2010)
so i need to somehow guarantee the order in which the update takes place (order by date).

thank u

---

for this requirement isnt below enough

SELECT t.operationtype,t1.Amt,t.date
FROM Table t
CROSS APPLY (SELECT SUM(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END) AS Amt
FROM Table
WHERE date < = t.date
)t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

UPDATE t
SET  t.Amt=t1.Amt
FROM Table t
CROSS APPLY (SELECT SUM(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END) AS Amt
FROM Table
WHERE date < = t.date
)t1

quote:

---

Originally posted by visakh16

then its just a matter of converting above select to update

UPDATE t
SET  t.Amt=t1.Amt
FROM Table t
CROSS APPLY (SELECT SUM(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END) AS Amt
FROM Table
WHERE date < = t.date
)t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

ok. but do you know that this type of 3 part update is not reliable? see last part in link below under I broke it heading

http://www.sqlservercentral.com/articles/T-SQL/68467/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Declare @Tbl table(rowID int Primary Key clustered,Amount money, RunningAmount money,errorCheck bit)

--Just using sysobjects to populate my table for this example
insert into @TBL(RowID,Amount)
Select Row_Number() over (Order by Object_ID) as RowID,System_Type_ID as RandomMoney
from sys.columns
order by RowID

Declare @Anchor int,@RunningTotal money,@IsError Bit,@CurrentRow int

set @CurrentRow = 0

Update a
set @Anchor = a.RowID
,@CurrentRow = @CurrentRow + 1
, @RunningTotal  = a.RunningAmount = --Use any logic you want, this just adds when odd and subtracts when even rows
							case when abs(a.RowID) %2 = 1 then isnull(@RunningTotal,0) + a.Amount
							else isnull(@RunningTotal,0) - a.Amount
							end
,@isError = a.errorCheck =  case when a.RowID <> @CurrentRow or @IsError = 1 then 1 else 0 end
from 
@TBL a


if @IsError = 1
Begin
	Select 'THERE WAS A ERROR' as Result
End
else
Begin
	Select 'Successful' as Result
end 
select * from @Tbl