使用row_counter值更新表

Question

我觉得这应该很容易，但我遇到了最难的时候。我正在使用SQL服务器。我正试图从以下查询中获得输出，以在更新语句中使用，以添加到我的表:

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;

主键是con_id。输出如下所示:

输入图片描述

我希望row_counter列是一个永久列在我的表名为COUNTER(我已经添加到我的表…我只需要更新它)。我试过了，但是我得到了一个错误，说

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