How do I update an existing table with a few hundred records with a unique ID

Question

I am having trouble updating an existing table (with a few hundred records) with a unique ID

The table is called BHIVE.ProjectDataGroupDetail and has a few hundred records. The Field I'd like to update is called ProjectDataGroupDetailID. I am also updating a 2nd field with the value '1'.

My stored procedure is:

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

I am getting the error :

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

Answer 1

You can use MERGE statement for this.

db<>fiddle

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
;

Answer 2

Can you try

UPDATE
BHIVE.ProjectDataGroupDetail

SET ProjectDataGroupDetailID = RowNumber
FROM
(
SELECT
Row_Number() OVER (ORDER BY SampleProjects) Rownumber
FROM
BHIVE.ProjectDataGroupDetail 
) source