Given the following example table schema and data:

create table Example(
   Id int not null,
   Col1 int not null,
   Col2 uniqueidentifier null,
);

insert into Example 
values 
(1, 1, NULL), (2, 1, NULL), (3, 1, NULL), (4, 2, NULL), (5, 3, NULL), (6, 3, NULL)

Which generates:

I would like help, if possible, in writing a script which would update Col2 for all rows with the same newid() based on the same value of Col1.

Ex of que table after the script is executed:

Much appreciated for any help

CodePudding user response：

One way would be to do it in two steps.

UPDATE Example
SET Col2 = NEWID()

UPDATE e1
SET Col2 = e2.Col2
FROM Example e1
CROSS APPLY (SELECT TOP 1 *
             FROM Example
             WHERE Col1 = e1.Col1
             ORDER BY Col2) e2

CodePudding user response：

This can also be done with a common table expression. The UNION is required so that the subquery is materialized.

WITH cte AS
(
    SELECT Col1, NEWID() AS UniqueID
    FROM Example
    GROUP BY Col1
    UNION
    SELECT NULL, NEWID()
)
UPDATE e
SET Col2 = cte.UniqueID
FROM Example e
INNER JOIN cte ON cte.Col1 = e.Col1