CODEWITHSUNDEEP
sqlsqliteduplicates
Gulbahar
Gulbahar

Reputation: 5537

How to keep only one row of a table, removing duplicate rows?

I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each.

The following lists the duplicates, but I don't know how to delete the duplicates and just keep one:

SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;

Thank you.

Upvotes: 25

Views: 101290

Answers (10)

Pidoski
Pidoski

Reputation: 71

DELETE FROM tablename WHERE ID IN( SELECT MAX(ID) ID FROM tablename GROUP BY IDNumber HAVING COUNT(IDNumber) > 1 )

Upvotes: 1

subash pandey
subash pandey

Reputation: 63

show record

SELECT `page_url`,count(*) FROM wl_meta_tags GROUP BY page_url HAVING count(*) > 1

delete record

DELETE FROM wl_meta_tags 
WHERE meta_id NOT IN( SELECT meta_id 
FROM ( SELECT MIN(meta_id)AS meta_id FROM wl_meta_tags GROUP BY page_url HAVING COUNT(*) > 1 )AS a ) 
AND meta_id NOT IN( (SELECT ids FROM (
SELECT MIN(meta_id)AS ids FROM wl_meta_tags GROUP BY page_url HAVING COUNT(*) =1 )AS a1 ) )

Source url

Upvotes: 0

Er Pkumar soni
Er Pkumar soni

Reputation: 320

if you want to remove duplicate record from table.

CREATE TABLE tmp SELECT lastname, firstname, sex
FROM user_tbl;
GROUP BY (lastname, firstname);

DROP TABLE user_tbl;

ALTER TABLE tmp RENAME TO user_tbl;

Upvotes: 0

user3125000
user3125000

Reputation: 1

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY [emp_id] ORDER BY [emp_id]) AS Row, * FROM employee_salary
)


DELETE FROM CTE
WHERE ROW <> 1

Upvotes: -1

Akhil Singh
Akhil Singh

Reputation: 730

delete dup row keep one table has duplicate rows and may be some rows have no duplicate rows then it keep one rows if have duplicate or single in a table. table has two column id and name if we have to remove duplicate name from table and keep one. Its Work Fine at My end You have to Use this query.

DELETE FROM tablename
WHERE id NOT IN(

 SELECT id FROM
(
    SELECT MIN(id)AS id
    FROM tablename
    GROUP BY name HAVING 
    COUNT(*) > 1
)AS a )
AND id NOT IN(
(SELECT ids FROM
(
SELECT MIN(id)AS ids
    FROM tablename
    GROUP BY name HAVING 
    COUNT(*) =1
)AS a1
)
)

before delete table is below see the screenshot: enter image description here after delete table is below see the screenshot this query delete amit and akhil duplicate rows and keep one record (amit and akhil):

enter image description here

Upvotes: 0

AnyKey
AnyKey

Reputation: 77

You can join table with yourself by matched field and delete unmatching rows

DELETE t1 FROM table_name t1 
LEFT JOIN tablename t2 ON t1.match_field = t2.match_field
WHERE t1.id <> t2.id;

Upvotes: 0

Lauri Lubi
Lauri Lubi

Reputation: 549

If we want to see first which rows you are about to delete. Then delete them.

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

Full example at http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

Upvotes: 0

Roee Adler
Roee Adler

Reputation: 33980

See the following question: Deleting duplicate rows from a table.

The adapted accepted answer from there (which is my answer, so no "theft" here...):

You can do it in a simple way assuming you have a unique ID field: you can delete all records that are the same except for the ID, but don't have "the minimum ID" for their name.

Example query:

DELETE FROM members
WHERE ID NOT IN
(
    SELECT MIN(ID)
    FROM members
    GROUP BY name
)

In case you don't have a unique index, my recommendation is to simply add an auto-incremental unique index. Mainly because it's good design, but also because it will allow you to run the query above.

Upvotes: 59

G Berdal
G Berdal

Reputation: 1164

We have a huge database where deleting duplicates is part of the regular maintenance process. We use DISTINCT to select the unique records then write them into a TEMPORARY TABLE. After TRUNCATE we write back the TEMPORARY data into the TABLE.

That is one way of doing it and works as a STORED PROCEDURE.

Upvotes: 0

Paul Dixon
Paul Dixon

Reputation: 300825

It would probably be easier to select the unique ones into a new table, drop the old table, then rename the temp table to replace it.

#create a table with same schema as members
CREATE TABLE tmp (...);

#insert the unique records
INSERT INTO tmp SELECT * FROM members GROUP BY name;

#swap it in
RENAME TABLE members TO members_old, tmp TO members;

#drop the old one
DROP TABLE members_old;

Upvotes: 4

Related Questions