CODEWITHSUNDEEP
sqlsql-server
Harsh Bilodiya
Harsh Bilodiya

Reputation: 15

SQL - Generate a UniqueIdentifier based on rows value

I want to generate unique ids based on what the value of a certain row is:

I have row1 and I want to generate new unique values based on row. Please see example below:

ROW 1        ROW2
======       =====
A101517 --> 68F66F3A-616B-45AA-B0EE-AC1B01040596
A101517 --> 68F66F3A-616B-45AA-B0EE-AC1B01040596

A233446 --> 56B79D73-0DBC-4EDA-8F8A-A76300C741AB
A233446 --> 56B79D73-0DBC-4EDA-8F8A-A76300C741AB 
A233446 --> 56B79D73-0DBC-4EDA-8F8A-A76300C741AB

A110027 --> 3EF0A1B5-B634-4549-9227-AA0F014040A2

Current solution:

UPDATE ROW2 SET ROW2 = NEWID();

THis is generating new values for each record. I want the same newid() when matching row1.

Upvotes: 0

Views: 61

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

You could do an update join from your table to a CTE which generates single GUID value for each distinct ROW1 value.

WITH cte AS (
    SELECT DISTINCT ROW1, NEWID() AS ROW2
    FROM yourTable
)

UPDATE t1
SET ROW2 = t2.ROW2
FROM yourTable t1
INNER JOIN cte t2
    ON t2.ROW1 = t1.ROW1;

Upvotes: 2

Related Questions