Updating a table in date order

Question

How do i update a table in date order, I have a date field, TransactionDate, some column values are dependant on previous values, how do I itterate through a table to update in it date order.

I have tried a few things but keep coming across "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

I have date ranges I want to re-process, changing older values means recalculating the newer values.

UPDATE M
SET M.AvgGain = CASE WHEN M.Price > Previous.Price THEN ( ISNULL( Previous.AvgGain * 13.0, 0 ) + ( M.Price - Previous.Price )) / 14.0 ELSE  ( ISNULL( Previous.AvgGain * 13.0, 0 ) / 14.0 )END
FROM SalesData M
JOIN SalesData AS Previous ON ( SELECT TOP 1 PK FROM SalesData WHERE AbbrevID = M.AbbrevID AND TransactionDate < M.TransactionDate ORDER BY TransactionDate DESC) = Previous.PK
WHERE M.AbbrevID IN ( SELECT PK FROM SalesData  WHERE AbbrevID = ( SELECT PK FROM SalesPeople WHERE Abbreviation = 'Fred') ) AND TransactionDate BETWEEN '1996-02-23' and '1996-05-08'

When i insert the order by TransactionDate into the Join it sulks at me

Please edit your question to provide sample data and what you want to do. Otherwise, your question is too vague. — Gordon Linoff, Jun 03 '14 at 11:17
Parenthesis are NOT closed correctly in the query, the last `AND` is for the outermost query or inner query? — sallushan, Jun 03 '14 at 12:07
The last And is in the correct place, but you are correct there is an extra bracket tagged on the end, I will amend. — LogicalHillwalker, Jun 03 '14 at 12:15

score 3 · Answer 1 · answered Jun 03 '14 at 11:40

I would probably go with cursor if you need to iterate one row at a time:

DECLARE @c_mem INT -- for example some numbeer of day, ie the calcuation you need to make from previous rows
SET @c_mem = 0 -- some initial value

DECLARE db_cursor CURSOR FOR  
SELECT [your_table_UID_field]
FROM [your_table_name]
ORDER BY TransactionDate

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @c_uid  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @c_mem=[some calculation based on c_mem itself and the current transac date]
       UPDATE [your_table_name]
       SET TransactionDate=[some calculation based on @c_mem, eg transac date + c_mem days]
       WHERE [your_table_UID_field]=@c_uid;

       FETCH NEXT FROM db_cursor INTO @c_uid  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Is this what you're looking for?

score 0 · Answer 2 · answered Jun 03 '14 at 12:14

Try with CROSS APPLY

UPDATE    M
    SET M.AvgGain = CASE WHEN M.Price > Previous.Price THEN 
                        ( ISNULL( Previous.AvgGain * 13.0, 0 ) + ( M.Price - Previous.Price )) / 14.0 
                    ELSE  
                        ( ISNULL( Previous.AvgGain * 13.0, 0 ) / 14.0 )
                    END
FROM    SalesData AS M
        CROSS APPLY (
            SELECT    TOP 1 AvgGain, Price
            FROM      SalesData
            WHERE     AbbrevID = M.AbbrevID AND TransactionDate < M.TransactionDate 
            ORDER BY TransactionDate DESC
        ) AS Previous
WHERE    M.AbbrevID IN ( 
                    SELECT     PK 
                    FROM     SalesData  
                    WHERE     AbbrevID = ( SELECT PK FROM SalesPeople WHERE Abbreviation = 'Fred')      
                )
        AND TransactionDate BETWEEN '1996-02-23' and '1996-05-08'

score -1 · Answer 3 · edited May 23 '17 at 10:28

-1

In SQL server SQL2005+, you can use update table 'order by' by using 'with' and 'over', like the following:

With XYX As
(
  SELECT id,Field2, ROW_NUMBER() 
           OVER (ORDER BY YourDateField DESC) AS RN
  FROM YourTable
)
UPDATE XYX SET Field2=RN

reference

edited May 23 '17 at 10:28

Community

1
1

answered Jun 03 '14 at 11:24

Adel

1,468
15
18

Updating a table in date order

3 Answers3