Duplicate Records - Setting other Column Values

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 2000 Forums

Transact-SQL (2000)

Duplicate Records - Setting other Column Values

Author

Topic

BigKaTooMer
Starting Member

3 Posts

Posted - 2006-12-11 : 07:02:25

Hi there. This is my first post here because I am well and truely stumped and would really appreciate some help!

I have a SQL Server table containing customer records.
I have hundreds of records where the record's Customer Name is duplicated many times.
I do not wish to delete the duplicates, but update the Customer ID field of each duplicate with that of one of the matches.

For example:

I want to go from:

Customer Name Customer ID

Bob 1
Bob 2
James 3
James 4
James 5
Bob 6
Betty 7

To:

Customer Name Customer ID

Bob 1
Bob 1
James 3
James 3
James 3
Bob 1
Betty 7

Any ideas of the SQL to get here would be greatly appreciated. I can identify the dupes, and count the occurences of them, but not update them to the Customer number of one of them.

I hope this is clear enough for someone to help me!

Many thanks,

Will

SQL Newbie!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 07:09:42

[code]declare @t table
(
custid int,
custname varchar(20)
)

insert @t
select 1, 'Bob' union all
select 2, 'Bob' union all
select 3, 'James' union all
select 4, 'James' union all
select 5, 'James' union all
select 6, 'Bob' union all
select 7, 'Betty'

update t1
set custid = (select min(custid) from @t t2 where t2.custname = t1.custname)
from @t t1

select * from @t[/code]

Note: I assume that CustID is not an identity column.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BigKaTooMer
Starting Member

3 Posts

Posted - 2006-12-11 : 09:26:14

Harsh,

Thanks for such a swift reply! Much appreciated.

Obviously, pasting your SQL as written works perfectly in context and that is just the job I want to do, though I do need to write the result to a table.

Howver, when it came to interpreting the result to my specific problem, I have used the SQL you published but I don't think I've got it correctly as it makes no changes.

My table is called EIM_CUSTOMERS and I only have one table so far, so the @t and the t1 and t2 variables have thrown me a bit. could you please explain what the refer to? Is t2 a 'create like' table for the new set of records?

And you are correct, CustId is not an Identity column.

Thanks again for your help with this!

SQL Newbie!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 09:28:52

[code]update t1
set t1.custid = (select min(t2.custid) from EIM_CUSTOMERS t2 where t2.custname = t1.custname)
from EIM_CUSTOMERS t1[/code]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 09:33:39

T1 and T2 are called Aliases and they saves SQL Devs from having to type long and boring table names again and again. @t is a table variable and it is created just for this sample code, you can paste your original table name in it's place.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BigKaTooMer
Starting Member

3 Posts

Posted - 2006-12-11 : 10:01:58

Job Done. Thanks to you both for prompt and relevant support. It's comforting to know that expert knowledge is at hand!

Thanks,

Will

SQL Newbie!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources