Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Removing Duplicate Records Expand / Collapse
Author
Message
Posted Saturday, April 19, 2008 10:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:47 PM
Points: 193, Visits: 94
There is no need to create temporary tables, very helpful when deleting from large tables, you can delete the duplicate records using inner join and setting the rowcout to 1, the modified version of script.

CREATE TABLE #phonebook (
[phonenumber] [varchar] (30),
[firstname] [varchar] (30),
[lastname] [varchar] (30),
[company] [varchar] (100)
)
INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'
-- Duplicate insert 1
INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'
-- Duplicate insert 2
INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'

--Show Duplicate Phonenumbers in Phonebook
SELECT phonenumber, COUNT(*) FROM #phonebook
GROUP BY phonenumber HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

SET ROWCOUNT 1
SELECT @@rowcount
WHILE @@rowcount > 0
DELETE pb FROM #phonebook as pb
INNER JOIN
(SELECT phonenumber
FROM #phonebook
GROUP BY phonenumber HAVING count(*) > 1)
AS c ON c.phonenumber = pb.phonenumber
SET ROWCOUNT 0


SELECT * FROM #phonebook

DROP TABLE #phonebook



Kindest Regards,

Syed
Sr. SQL Server DBA
Post #487617
Posted Sunday, April 20, 2008 11:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:23 AM
Points: 242, Visits: 939
I agree you do not really need the temporary table unless you want it to be able to review results before making final changes to the "live" database.

If you want to forego the temp table, below is a variation (without loops or temp tables) for removing duplicates if you have some other criteria to determine which duplicate to keep. If you have to pick off a completely identical record, then I guess you would have to do some sort of @@Rowcount function as shown in a previous posting.

If you do have criteria to pick out which record(s) to remove then the removal is a simple Delete statement. Again if the records are exact duplicates then it is a coin-toss to find which one to take out so you would need a rowcount, cursor kind of method, index of columns with dup key (as in the original article and some posts), or a select distinct into a temporary table following that method.


The Delete Statement (for duplicate phone values with differing pdate values)
DELETE p1
FROM @phonenum p1
INNER JOIN @phonenum p2
ON p1.phone = p2.phone and p1.pdate > p2.pdate

The Delete statement in test code
Declare @phonenum table (phone varchar(10), pdate int) 

insert -- set up for test
into @phonenum (phone,pdate)
select '1',1 union all
select '2',2 union all
select '3',3 union all
select '3',2 union all
select '4',2 union all
select '5',1 union all
select '5',7 union all
select '5',3 union all
select '6',1


select 'original table', phone, pdate -- show before picture
from @phonenum


/* Remove the dups from the original table based on oldest date */

DELETE p1
FROM @phonenum p1
INNER JOIN @phonenum p2
ON p1.phone = p2.phone and p1.pdate > p2.pdate



select 'after delete', phone, pdate -- show after picture
from @phonenum

Toni
Post #487647
Posted Sunday, April 20, 2008 4:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:33 PM
Points: 85, Visits: 260
I would be hesitant about deleting duplicates in the same statement that finds them. If the table was non trivial size (ie. you didnt want to hand list the record keys to be deleted)

Any long running scan of the table looking for duplicates could cascade its lock to table level.

Generated record deletes in small transactions would have less impact if the system was live.
Post #487662
Posted Monday, April 21, 2008 1:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 23, 2014 2:40 AM
Points: 55, Visits: 149
And ofcourse you can create a CHECKSUM computed column which is calculated from all other fields. So you can determine all the duplicate records easily, they are all give the same checksum value.

But unfortunately CHECKSUM is 32 bit integers, so if you have too much records in your table it may give the same checksum value.

You can also use the

--create a checksum field to see duplicates
ALTER TABLE Phonebook
ADD chkValue AS checksum(first_name, last_name, phonenumber)
--select and/or delete duplicates
...
--create unique index so no more dup. can be inserted
CREATE INDEX chkUnique ON Phonebook (chkValue) WITH IGNORE_DUP_KEY

Don't forget, if you have thousands of records then there can be different rows that give the same checksum value.
Post #487733
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse