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