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

delete duplicates Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 3:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:02 AM
Points: 846, Visits: 303
Because you say your table is big and you don't seem to have the right indexes, you can use the following as an alternative to doing a select distinct or a row_number() over():

Create a new table with a unique index on the colums where you don't want duplicates but use the IGNORE_DUP_KEY = ON option (see http://msdn.microsoft.com/en-us/library/ms188388.aspx). Copy all records from the old table to the new one, drop the old table and rename the new one.

Done.


Regards,

Willem
http://wschampheleer.wordpress.com
Post #855024
Posted Thursday, March 24, 2011 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 24, 2011 6:02 AM
Points: 1, Visits: 3
I have found good article on deleting duplicate record. You can see T-SQL Query To Delete Duplicate Records Among Identical Rows In A Table....
Post #1083207
Posted Thursday, March 24, 2011 11:06 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:32 AM
Points: 194, Visits: 1,105
Can be done using temp table

SELECT EmpID,EmpName INTO #temp
FROM Employees
GROUP BY EmpID,EmpName
HAVING COUNT(*)>1

DELETE Employees FROM Employees a INNER JOIN #temp b ON
a.EmpID=b.EmpID

INSERT INTO Employees(EmpID,EmpName)
SELECT EmpID,EmpName FROM #temp
Post #1083745
Posted Thursday, March 24, 2011 11:07 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:32 AM
Points: 194, Visits: 1,105
Can be done using temp table for your reference

SELECT EmpID,EmpName INTO #temp
FROM Employees
GROUP BY EmpID,EmpName
HAVING COUNT(*)>1

DELETE Employees FROM Employees a INNER JOIN #temp b ON
a.EmpID=b.EmpID

INSERT INTO Employees(EmpID,EmpName)
SELECT EmpID,EmpName FROM #temp
Post #1083746
Posted Friday, March 25, 2011 12:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
A simple way to delete dups that works on versions earlier than 2005 is:

DELETE EMP
FROM Employees EMP
INNER JOIN
(SELECT MIN(EmpID) AS EmpID, EmpName
FROM Employees
GROUP BY EmpName
HAVING COUNT(*) > 1
) AS DUP ON
EMP.EmpName = DUP.EmpName
AND EMP.EmpID <> DUP.EmpID

Todd Fifield
Post #1084275
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse