Updating uniqueidentifier column with same value for rows with matching column value-CodePudding

I need a little help. I have this (simplified) table:

ID	Title	Subtype	RelatedUniqueID
1	My Title 1	1	NULL
2	My Title 2	1	NULL
3	My Title 3	2	NULL
4	My Title 4	2	NULL
5	My Title 5	2	NULL
6	My Title 6	3	NULL

What I am trying to accomplish is generating the same uniqueidentifier for all rows having the same subtype.

So result would be this:

ID	Title	Subtype	RelatedUniqueID
1	My Title 1	1	439753d3-9103-4d0e-9dd0-569dc71fd6a3
2	My Title 2	1	439753d3-9103-4d0e-9dd0-569dc71fd6a3
3	My Title 3	2	d0f08203-1197-4cc7-91bb-c4ca34d7cb0a
4	My Title 4	2	d0f08203-1197-4cc7-91bb-c4ca34d7cb0a
5	My Title 5	2	d0f08203-1197-4cc7-91bb-c4ca34d7cb0a
6	My Title 6	3	055838c6-a814-4bd1-a859-63d4544bb449

Requirements

One query to update all rows at once
The actual table has many more rows with hundreds of subtypes, so manually building a query for each subtype is not an option

Using SQL Server 2017

Thanks for any assist.

CodePudding user response：

Because newid() is applied per-row, you have to generate the values first, so this has to involve the use of a temporary or permanent table to store the correlated ID>Subtype value.

So first you need to generate the GUID values per Subtype :

with subtypes as (
    select distinct subtype
    from t
)
select Subtype, NewId() RelatedId into #Id
from subtypes

And then you can use an updatable CTE to apply these to your base table:

with r as (
    select t.*, id.RelatedId
    from #id id
    join t on t.subtype=id.Subtype
)
update r
set relatedUniqueId=RelatedId

See example DB<>Fiddle

CodePudding user response：

You can use an updatable CTE with a window function to get this data:

with r as (
    select t.*,
      RelatedId = first_value(newid()) over (partition by t.Subtype order by ID rows unbounded preceding)
    from t
)
update r
set relatedUniqueId = RelatedId;

db<>fiddle

I warn though, that newid() is somewhat unpredictable in when it is calculated, so don't try messing about with a joined update (unless you pre-save the IDs like @Stu has done).

For example, see this fiddle, the IDs were calculated differently for every row.

CodePudding user response：

I have found the single query solution.

Pre-requirement for this to work is that RelatedUniqueID must already contain random values. (e.g. set default field value to newid)

UPDATE TestTable SET ForeignUniqueID = TG.ForeignUniqueID FROM TestTable TG INNER JOIN TestTable ON TestTable.SubType = TG.SubType

Update As Stu mentions in the comments, this solution might affect performance on large datasets. Please keep that in mind.