deleting duplicate records

Question

Is there some way by which i can delete duplicate record that exist in table.I just want one record and rest of them to be deleted by query .Is this can be done through a query . please suggest

There is, but you should learn to both search for existing answers and accept answers to your existing questions. — Matt Mitchell, Aug 02 '10 at 07:06
What RDBMS are you using? Also have any of your last 10 questions been answered satisfactorily? If so can you accept answers to these? — Martin Smith, Aug 02 '10 at 09:15
If you are using MySQL, you may want to check out this post from yesterday: http://stackoverflow.com/questions/3383898/remove-duplicates-using-only-a-mysql-query — Daniel Vassallo, Aug 02 '10 at 09:39

score 5 · Accepted Answer · edited Aug 08 '12 at 07:57

Try this -

Add id column as IDENTITY to your table first -

alter <tablename> add id INT IDENTITY

Then use below query to delete duplicate records -

delete 
FROM <tablename>
WHERE id IN
(
SELECT MAX(id)
FROM <tablename>
GROUP BY <columnnames defining uniqueness>
having count ( * )   > 1
)

Please take into consideration, that it only deletes one duplicate of existing row. If there are i.e. three rows that share grouping condition - this will not work, unless you execute query multiple times (until no rows were deleted) or change the delete condition.

score 0 · Answer 2 · answered Aug 02 '10 at 07:16

0

use distinct :

SELECT distinct * FROM Table ....

answered Aug 02 '10 at 07:16

Rawhi

6,155
8
36
57

score 0 · Answer 3 · answered Aug 02 '10 at 09:22

It Basically depends on the structure of your table,the number of contraints it has and the number of columns in your primary key if it has any.

You need to find the optmized query which will identify the unique records based on the above constraints you have and then most impotantly you need to consider the time taken to delete those duplicates iwth your query.

So nobody can comment unless you publish the complete structure and some example duplicate data.

Hope this little input helps.

score 0 · Answer 4 · edited Aug 09 '12 at 07:05

Here are sample example of delete duplicate records

Create table #Test (colA int not null, colB int not null, colC int not null, id int not null identity) on [Primary]
GO
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)

INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)

INSERT INTO #Test (colA,colB,colC) VALUES (4,5,6)
GO
Select * from #Test
GO
Delete from #Test where id <
(Select Max(id) from #Test t where #Test.colA = t.colA and
#Test.colB = t.colB and
#Test.colC = t.colC)
GO
Select * from #Test
GO

deleting duplicate records

4 Answers4

Linked

Related