带有row_counter值的更新表

Question

我觉得这应该很容易，但是我度过了最困难的时光。我正在使用SQL Server。 I'm trying to get the output from the following query to be used in an update statement to add to my table:

SELECT cons_id, credited_date,
    ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
FROM fy23_jul_aug_anniv_jv;

My primary key is cons_id. The output looks like this:

enter image description here

I want the row_counter column to be a permanent column在我的表中称为计数器（我已经添加到表中...我只需要更新它）。我已经尝试过，但是我有一个错误说

sql命令未正确结束。

UPDATE fy23_jul_aug_anniv_jv 
SET counter = b.row_counter
FROM (SELECT cons_id, ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
        FROM fy23_jul_aug_anniv_jv) AS b
WHERE b.cons_id = cons_id;   

Answer 1

您可以使用常见表表达式（CTE）

WITH CTE AS (
    SELECT counter ,ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
    FROM fy23_jul_aug_anniv_jv
)
UPDATE CTE SET counter = Row_Counter