Resolve Same Repeating Name. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Resolve Same Repeating Name.

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-12-21 : 22:54:15

Table A

Company branch personname ID
A 12 Mike
A 13 Mike
B 11 Andrew
C 08 Don

Same Company with different branch can have same person. How do i update ID Column such if same person exists same newid() should be created as:

OutPut:
Company branch personname ID
A 12 Mike 2620DCB7-3A09-481E-A061-9E51FBA6FFB9
A 13 Mike 2620DCB7-3A09-481E-A061-9E51FBA6FFB9
B 11 Andrew 1370DCB7-3A09-ROFE-A061-9E51FBA6FFB9
C 08 Don 1890DCB7-3A09-4OEF-A061-9E51FBA6FFB9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-21 : 23:53:21

You can do it with nested derived tables, something like this:


SELECT dt2.Company, yt.Branch, dt2.PersonName, dt2.ID
FROM 
(
	SELECT Company, PersonName, NEWID() AS ID
	FROM 
	(
		SELECT DISTINCT Company, PersonName 
		FROM YourTable
	) dt1
) dt2
JOIN YourTable yt
ON dt2.Company = yt.Company AND dt2.PersonName = yt.PersonName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-22 : 00:00:32

Try like this

create table #sample (Company varchar(10),
branch int, personname varchar(10),ID varchar(100))

insert into #sample (Company, branch, personname)

select 'A' ,12, 'Mike' union all
select 'A' ,13, 'Mike' union all
select 'B' ,11, 'Andrew' union all
select 'c' ,8, 'Don'

select * from #sample

declare @new_id varchar(64)

set @new_id=newid()

update #sample set id=@new_id where Company=(
select company from #sample group by
company,personname having count(*)>1)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-22 : 00:19:12

quote:
Originally posted by tkizer

You can do it with nested derived tables, something like this:

SELECT dt2.Company, yt.Branch, dt2.PersonName, dt2.ID
FROM
(
SELECT Company, PersonName, NEWID() AS ID
FROM
(
SELECT DISTINCT Company, PersonName
FROM YourTable
) dt1
) dt2
JOIN YourTable yt
ON dt2.Company = yt.Company AND dt2.PersonName = yt.PersonName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

I would think that as well. It would seem NEWID() is kindof "trippy" in that it does not seem to care that the set is derived.


SELECT 'A' AS Company, 12 AS branch, 'Mike' as personName, CONVERT(varchar(255), null) as ID
INTO #TableA 
	UNION ALL
	SELECT 'A', 13, 'Mike', CONVERT(varchar(255), null) as ID  
	UNION ALL
	SELECT 'B', 11, 'Andrew', CONVERT(varchar(255), null) as ID 
	UNION ALL
	SELECT 'C', 08, 'Don', CONVERT(varchar(255), null) as ID


SELECT dt2.Company, yt.Branch, dt2.PersonName, dt2.ID
FROM 
(
	SELECT Company, PersonName, NEWID() AS ID
	FROM 
	(
		SELECT DISTINCT Company, PersonName 
		FROM #TableA
	) dt1
) dt2
JOIN #TableA yt
ON dt2.Company = yt.Company AND dt2.PersonName = yt.PersonName

DROP TABLE #TableA

I just brute forced the thing by persisting the derivation to a temp container then performing the update.


--assign the newID's
SELECT personName, CAST(NEWID() as VARCHAR(255)) as ID
INTO #n
FROM #TableA
GROUP BY personName

--perform the update
UPDATE t
SET t.ID = n.ID
FROM #TableA t
JOIN #n n ON n.personName = t.personName

P.S. Its been a while Tara. Great to see you continuing to help others so generously.

BTW. Congrats on MVP!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 02:12:06

As you use SQL Server 2005, it can be simplified as


select 
	Company, branch, personname,
	max(cast(newid() as varchar(36))) over (partition by Company,personname) as ID
from 
	your_table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources