sql table sequence update help

Question

Hi,

I have a column named [list] which I need to update with sequence number partitioned by [id] order by [comment_timestamp]

It works in a select statement:
SELECT row_number() over (partition by id order by comment_timestamp)
FROM dbo.comments
but I don't know how to put this in an update statement?

The script for the table look like this
CREATE TABLE [dbo].[comments](
[id] [int] NOT NULL,
[list] [int] NULL,
[Comment] varchar NULL,
[domain] varchar NULL,
[comment_timestamp] datetime2 NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I understand this might be very easy but could someone please assist? Appreciate all help

Answer 1

Check this command:

update t
set list = rn
from
(
    select list, row_number() over (partition by id order by comment_timestamp) as rn
    from dbo.comments
) t

If you are interested. you will also find an alternative based on "CTE".

Answer 2

Hi @Micke ，

Welcome to the microsoft TSQL Q&A forum!

Since window functions can only be used in SELECT or ORDER BY statements, you need to use subqueries or cte. The cte mentioned by Viorel-1 is as follows:

;WITH cte as  
(SELECT [list],row_number() over (partition by id   
order by comment_timestamp) rr   
FROM [dbo].[comments])  
  
UPDATE cte  
SET [list]= rr  
FROM cte  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.