March 15, 2005 at 10:37 am
What is the better way to update (without using cursors) a field for every row in a table if this field depends on the actual row's data and the previous row value for this field.
For example:
OrderID ProductQuantity Accumulated
1 3 3
2 5 8
3 4 12
4 200 212
Thanks in advance,
Felix
March 15, 2005 at 10:48 am
Update YourTable
Set Accumulated = dtAccumulated.Accumulated
From YourTable As a
Inner Join
(
Select o1.OrderID, Sum(o2.ProductQuantity) As Accumulated
From YourTable as o1,
YourTable As o2
Where o2.OrderID <= o1.OrderID
Group By o1.OrderID
) dtAccumulated
On a.OrderID = dtAccumulated.OrderID
March 15, 2005 at 10:48 am
Yes there's a way but I don't see why you would want to do this in this case. I'm under the impression that you are trying to do some reporting work, but that's only an impression.
What are you trying to do?
March 15, 2005 at 11:03 am
In fact I'm just trying to show the results in a dataaware control (DbGrid) using Borland Delphi, and if I use a Calculated field to accomplish this task, every time I move the cursor accross the table an OnCalcField event is triggered, causing performance issues. I just want to calculate the accum value once. In other way of things, I could have an actual "Accumulted" field in the table and move along the table updating this field before show the grid, but it's so slow when I have a lot of records.
March 15, 2005 at 11:03 am
In fact I'm just trying to show the results in a dataaware control (DbGrid) using Borland Delphi, and if I use a Calculated field to accomplish this task, every time I move the cursor accross the table an OnCalcField event is triggered, causing performance issues. I just want to calculate the accum value once. In other way of things, I could have an actual "Accumulted" field in the table and move along the table updating this field before show the grid, but it's so slow when I have a lot of records.
March 15, 2005 at 11:22 am
I have 0 experience in this context. But I assume that the data must be refreshed pretty often and by multiple users... I think you'll have to come up with a very well written trigger to update the data (I assume here that orders can be modified at some point changing the CalcField data entered after that order).
However the solution provided by PW will give you the correct data. Now it's just a matter of coding the update correctly.
March 15, 2005 at 11:38 am
Executing you query I got the error:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'dtAccumulated' does not match with a table name or alias name used in the query.
March 15, 2005 at 11:52 am
What is the result when you run this SQL ?
exec sp_dbcmptlevel 'YourDatabaseNameGoesHere'
March 15, 2005 at 11:59 am
The current compatibility level is 70.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply