CODEWITHSUNDEEP
mysqlsqldatabaseduplicatessql-delete
RoboPHP
RoboPHP

Reputation: 420

How to delete duplicates and leave one row in a table

I have table which looks like this. I want to delete duplicates and leave one row for each user. How do I go about this?

Table*

id     user 
Thango   1

Thango  1 

Samg     2

Samg     2

Results

id user

Thango   1

Samg     2

Upvotes: 1

Views: 63

Answers (2)

mburns
mburns

Reputation: 74

You use Common Table Expressions (CTE). For a better explanation I advise you to take a look into this url

A possible solution:

WITH CTE([user], 
duplicatecount)
AS (SELECT [user],
           ROW_NUMBER() OVER(PARTITION BY [user]
           ORDER BY [id]) AS DuplicateCount
    FROM dbo.[YourDataBase])
DELETE FROM CTE
WHERE DuplicateCount > 1

Upvotes: 0

GMB
GMB

Reputation: 222412

For this dataset, it is probably simpler to just empty and refill the table:

-- deduplicate into a temporary table
create table mytmp as select distinct id, user from mytable;

-- empty the original table (backup your data first!)
truncate table mytable;

-- refill the table from the temporary table
insert into mytable(id, user) select id, user from mytmp;

-- drop the temporary table
drop table mytemp;

Once this is done, you might consider creating a unique constraint on the table to avoid further duplicates:

alter table mytable
    add constraint myconstraint
    unique (id, user);

Upvotes: 2

Related Questions