Update a column with sequence number from 1 with a where and order by clauses

Question

I'm trying to execute the following SQL query to update a column called seq with numbering sequence for a particular id but it throws an error:

Incorrect syntax near the keyword 'ORDER'.

DECLARE @id INT 
SET @id = 0 

UPDATE T_TRNHIST 
SET @id = seq = @id + 1 
WHERE Acc='12344'
OPTION ( MAXDOP 1 )
ORDER BY Recid, trnDate

Where could I have gone wrong?

I don't think you can use `ORDER BY` in a `UPDATE` statement. https://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by — Amit Kumar, May 20 '19 at 06:46
Can you let us know, what is the objective behind such query? — Amit Kumar, May 20 '19 at 06:46

Zohar Peled · Answer 1 · 2023-06-28T18:28:52.790

2

You can't use order by in an update statement, and you should be very careful when using quirky updates, as they are very unpredictable.

A simple, trust worthy solution would be to use an updatable common table expression with row_number:

WITH CTE AS
(
    SELECT  seq,
            ROW_NUMBER() OVER(ORDER BY Recid, trnDate) As rn
    FROM T_TRNHIST
)

UPDATE CTE 
SET seq = rn

edited Jun 28 '23 at 18:28

answered May 20 '19 at 06:57

Zohar Peled

79,642
10
69
121

score 0 · Answer 2 · answered May 20 '19 at 06:56

You can try UPDATE ... FROM ... syntax, which allows using JOIN. Here's code snippet along with test data:

declare @tbl table (seq int, Acc varchar(10), RecId int, trnDate date);
insert into @tbl values
(null, '12344', 2, '2019-05-05'),
(null, '12344', 1, '2019-05-06'),
(null, '12344', 5, '2019-05-04'),
(null, '12344', 5, '2019-05-03'),
(null, '12355', 1, '2019-05-05');

select * from @tbl

update t1 set t1.seq = t2.rn
from @tbl t1 join (
    select row_number() over (order by RecId, trnDate) rn,
           trnDate,
           RecId,
           Acc
    from @tbl
    where Acc = '12344'
) t2 on t1.trnDate = t2.trnDate and t1.RecId = t2.RecId and t1.Acc = t2.Acc

select * from @tbl

Update a column with sequence number from 1 with a where and order by clauses

2 Answers2