Ok this is difficult to phrase, so here goes…
I am using MS SQL Server 2008 R2. I have a temp table that lets say has two already populated columns. There is a third empty column I want to populate based on the value of the first two columns. What I want to do is create a guid (using NEWUID()) for each matching combo of col1 and col2. Here is a visual example:
Lets say I have a temp table that looks like this initially:
Name Activity SpecialId
James Running
James Running
James Walking
John Running
John Running
John Walking
I want it to get updated with new GUIDs so that it looks like this:
Name Activity SpecialId
James Running SOMEFAKEGUID_1
James Running SOMEFAKEGUID_1
James Walking SOMEFAKEGUID_2
John Running SOMEFAKEGUID_3
John Running SOMEFAKEGUID_3
John Walking SOMEFAKEGUID_4
Notice how a new GUID is created for each matching pair. So the James/Running combo has the same GUID for all James/Running combos… and the John/Running also has the same GUID for the John/Running combos, but not the same GUID as the James/Running combos do.
I tried to make that as clear as possible, but hopefully that isn’t clear as mud!
Can someone show me what the SQL query would look like in order to update that temp table with the correct GUIDs?
Thanks in advance.
Ryan
Advertisement
Answer
Using NEWID() seems to be a pain. Using it a CTE creates a sperate ID, so you need some intermediary table instead.
Declare @Table as table (name varchar(20), activity varchar(20) , SpecialID uniqueidentifier)
Declare @DistinctTable as table (name varchar(20), activity varchar(20) , SpecialID uniqueidentifier)
INSERT INTO @Table
(name, activity)
values
('James','Running'),
('James','Running'),
('James','Walking'),
('John','Running'),
('John','Running'),
('John','Walking')
WITH distinctt
AS (SELECT DISTINCT name,
activity
FROM @Table)
INSERT INTO @DistinctTable
SELECT name,
activity,
Newid()
FROM distinctt
UPDATE @Table
SET specialid = dt.specialid
FROM @Table t
INNER JOIN @DistinctTable dt
ON t.activity = dt.activity
AND t.name = dt.name
SELECT * FROM @Table
Produces
name activity SpecialID
-------------------- -------------------- ------------------------------------
James Running AAA22BC5-51FE-43B3-8CC9-4C4A5B4CC981
James Running AAA22BC5-51FE-43B3-8CC9-4C4A5B4CC981
James Walking 1722B76B-5F17-4931-8D7C-2ECADB5A4DFD
John Running FBC1F86B-592D-4D30-ACB3-80DA26B00900
John Running FBC1F86B-592D-4D30-ACB3-80DA26B00900
John Walking 84282844-AAFD-45CA-9218-F7933E5102C6