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 «««12345»»

Deleting Duplicate Records Expand / Collapse
Author
Message
Posted Thursday, May 18, 2006 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 5, 2009 10:21 AM
Points: 1, Visits: 7
Cleanest and easiest way to clean up duplicates that I have seen.
Post #281224
Posted Sunday, March 11, 2007 10:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 7:17 PM
Points: 14, Visits: 56
A very good and helpful topic
Post #350613
Posted Sunday, March 11, 2007 11:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 31, 2013 4:35 AM
Points: 448, Visits: 136

Hi Ramakrishnan,

           The script you had provided was quite a bit useful but i have some reservations on this method. Actually i had drawn up a similar script but found that when the number of columns increase in the table, you will have to group by all the columns. is there any other method to delete the duplicate rows other than taking the stock into the temp table and again fetching it back to the physical table.

Post #350617
Posted Monday, March 12, 2007 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:33 AM
Points: 23, Visits: 104

My method for duplicate removal  may not suit every one. it offers the advantage if you have duplicate data but the identity column makes the data unique you can modify the select * to only the duplicated fields.

 

begin tran

Select Distinct * into [newtable] from [badtable]

truncate table [badtable]

insert into badtable select * from newtable

drop table [newtable]

 

ColinR

 

 

 

Post #350669
Posted Monday, March 12, 2007 9:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

Here's another method. This one adds an identity column, uses it, and then drops it.

ALTER TABLE dbo.employee ADD tempid INT IDENTITY(1, 1)
DELETE dbo.employee WHERE tempid NOT IN (SELECT MIN(tempid) FROM dbo.employee GROUP BY id, NAME, salary)
ALTER TABLE dbo.employee DROP COLUMN tempid




Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #350770
Posted Monday, April 16, 2007 3:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411

Or, if you use SQL Server 2005,

 

DELETE t1
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID
FROM Table1
) AS t1
WHERE RecID > 1

 




N 56°04'39.16"
E 12°55'05.25"
Post #358548
Posted Thursday, October 4, 2007 9:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 1:38 PM
Points: 63, Visits: 33
My question is this....

Please correct me if I'm wrong, or how to get around the issue, but how about a real-life scenario that I have:

I have been given a logical definition of a duplicate row to be a composite of 4 columns. However, there are actually 10 columns in the table. This data has duplicates in the logical composite key, but it's not necessarily a duplicate if you included the other data fields in the "group by". If I want to copy the data to the other "temp" table, I have to include all the columns in the "group by" or I will lose the other data, but in my case, I can't do that or I will miss some duplicates.
Post #406916
Posted Friday, June 13, 2008 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 8, 2013 1:54 AM
Points: 7, Visits: 34
sorry I read on msdn that 'Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server'. So this method will not work anymore, will it not?
Thanks
Gio
Post #516468
Posted Monday, August 25, 2008 7:03 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 21, 2013 12:42 AM
Points: 152, Visits: 198
Hi ,
I have tried duplicate record deletion without using temp tables.
And according to me its faster and a very short query.

CREATE TABLE EMP
(EMPID INT,EMPNAME VARCHAR(100))

INSERT INTO EMP VALUES (1,'A')
INSERT INTO EMP VALUES (1,'A')
INSERT INTO EMP VALUES (1,'A')
INSERT INTO EMP VALUES (2,'B')
INSERT INTO EMP VALUES (2,'B')
INSERT INTO EMP VALUES (3,'C')
INSERT INTO EMP VALUES (4,'D')
INSERT INTO EMP VALUES (4,'D')
INSERT INTO EMP VALUES (4,'D')
INSERT INTO EMP VALUES (4,'D')
INSERT INTO EMP VALUES (5,'E')
INSERT INTO EMP VALUES (5,'E')
INSERT INTO EMP VALUES (6,'F')
INSERT INTO EMP VALUES (6,'F')
INSERT INTO EMP VALUES (6,'F')
INSERT INTO EMP VALUES (6,'F')
INSERT INTO EMP VALUES (6,'F')
INSERT INTO EMP VALUES (7,'G')

SELECT * FROM EMP

SELECT @@ROWCOUNT
--(Initially the rowcount must be > 0 )
WHILE @@ROWCOUNT > 0
DELETE TOP(1) FROM EMP WHERE EXISTS
(SELECT E.EMPID ,COUNT(E.EMPID) FROM EMP E
WHERE EMP.EMPID=E.EMPID GROUP BY E.EMPID HAVING COUNT(E.EMPID) > 1)

SELECT * FROM EMP


Correct if wrong ........................
Post #558100
Posted Monday, August 25, 2008 7:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 21, 2013 12:42 AM
Points: 152, Visits: 198
Hi Peso,
i tried your code but it did not work.
Are you sure it works?

Thanks
Post #558119
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse