March 21, 2018 at 5:14 pm
What is the easiest way to achieve the same results as the provided query? i'm trying to update a table (temp) with the same guid for each group. The only thing I can think of is what is below but I'm sure there is something better.
create table temp
(
guid uniqueidentifier null
,id int null
)
insert into temp(id)
values (1)
,(1)
,(1)
,(2)
,(2)
,(2)
;with cte ([guid], id) as
(
select newid(), id
from temp
group by id
)
select * into #tmp from cte
update t
set t.[guid] = tmp.[guid]
from temp t
join #tmp tmp on
tmp.[id] = t.id
select * from temp
drop table #tmp
March 22, 2018 at 5:16 am
I think below is slightly simpler, and I think it does what you want, but I'm not sure why you would want to do this. Generally the uniqueid would be generated in a table somewhere probably one that has your ID as the primary key!
CREATE TABLE #temp ( guid UNIQUEIDENTIFIER NULL , id INT NULL )
INSERT INTO #temp ( id )
VALUES ( 1 ) , ( 1 ) , ( 1 ) , ( 2 ) , ( 2 ) , ( 2 )
-- join and update.
UPDATE #Temp SET #Temp.GUID = tGuid.NewGUID
FROM #Temp JOIN (SELECT ID, MAX(NEWID()) AS NewGUID FROM #Temp GROUP BY ID) tGuid
ON #Temp.ID = tGuid.ID
-- view the results.
SELECT * FROM #temp
March 22, 2018 at 7:00 am
allinadazework - Thursday, March 22, 2018 5:16 AMI think below is slightly simpler, and I think it does what you want, but I'm not sure why you would want to do this. Generally the uniqueid would be generated in a table somewhere probably one that has your ID as the primary key!
CREATE TABLE #temp ( guid UNIQUEIDENTIFIER NULL , id INT NULL )INSERT INTO #temp ( id )
VALUES ( 1 ) , ( 1 ) , ( 1 ) , ( 2 ) , ( 2 ) , ( 2 )-- join and update.
UPDATE #Temp SET #Temp.GUID = tGuid.NewGUID
FROM #Temp JOIN (SELECT ID, MAX(NEWID()) AS NewGUID FROM #Temp GROUP BY ID) tGuid
ON #Temp.ID = tGuid.ID-- view the results.
SELECT * FROM #temp
Perfect! Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply