Finding Duplicates and Adding Link

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

General SQL Server Forums

New to SQL Server Programming

Finding Duplicates and Adding Link

Author

Topic

aniko
Starting Member

25 Posts

Posted - 2011-11-30 : 00:53:00

Hi guys,

Probably been asked before but I couldn't find anything in search.

I would like to find duplicates in my database based on first name, surname and postcode.

I would like to write these out to a new table, but more importantly add a linking reference (any value) to each cluster of duplicates. E.g.

Record A and Record B are considered duplicates and both have a "link ref" of the same value, e.g. "4".

This way I can easily determine in the table which records are the same.

Is this possible via SQL? Do you have any examples?

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 01:24:43

its possible. On what basis you want to generate link ref value? is it just a id number generated in sequence for each set of duplicates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aniko
Starting Member

25 Posts

Posted - 2011-11-30 : 15:39:27

I'm not too concerned on how it's generated. Probably the easiest way! Do you have any suggestions?

What would this script look like in full?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:21:03

you can create a link table with following structure

linktable
--------------------
id int identity(1,1),
firstname varchar(100),
surname varchar(100),
postcode varchar(30)

then add a newcolumn to main table called linkid int

then insert records to link table using below


insert linktable (firstname,surname,postcode)
select firstname,surname,postcode
from maintable
group by firstname,surname,postcode
having count(*) >1

then using the generated id do update back to main table


update t
set t.linkid=l.id
from maintable t
inner join linktable l
on l.firstname = t.firstname 
and l.surname = t.surname 
and l.postcode = t.postcode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aniko
Starting Member

25 Posts

Posted - 2011-12-01 : 23:54:16

Thanks, I'll try this and let you know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 01:02:17

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources