sql - 如何使用具有唯一 ID 的数百条记录更新现有表

Question

我在更新具有唯一 ID 的现有表（包含数百条记录）时遇到问题

该表名为 BHIVE.ProjectDataGroupDetail，有几百条记录。我要更新的字段称为 ProjectDataGroupDetailID。我还在用值“1”更新第二个字段。

我的存储过程是：

ALTER PROCEDURE [BHIVE].[PopulateProjectDataGroupDetail]  
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

UPDATE BHIVE.ProjectDataGroupDetail
SET ProjectDataGroupDetailID = Row_Number() OVER (ORDER BY SampleProjects), ProjectDataGroup = 1 
    
End

我收到错误消息：

Windowed functions can only appear in the SELECT or ORDER BY clauses

标签： sqlsql-server-2017row-numberunique-id

Answer 1

您可以为此使用MERGE语句。

db<>小提琴

merge into ProjectDataGroupDetail as t
using (
  select id, row_number() over(partition by some_grouping_col order by id) as rn
  from ProjectDataGroupDetail
) as s
  on t.id = s.id
when matched then update
  set
    ProjectDataGroupDetailID = s.rn,
    ProjectDataGroup = 1
;