CODEWITHSUNDEEP
mysql
JDevil
JDevil

Reputation: 179

Removing duplicate records in mysql

How To delete duplicate record in Table which all record in same

id|name|address
1 |   a|b
1 |   a|b
1 |   a|b
1 |   a|b

Only One Record left after deleting

Upvotes: 0

Views: 61

Answers (3)

Pinguin895
Pinguin895

Reputation: 1011

you can use

ALTER IGNORE TABLE tablename ADD UNIQUE (id, name, address)

This will remove all duplicate entries and will also prevent new duplicates to get inserted. If you want to insert new duplicates later you can simply drop it again.

But like Gordon Linoff already said you should think about adding an auto-increment key...

Upvotes: 0

Girish
Girish

Reputation: 12127

AS you asking all records are same then keep only single record and delete others 

DELETE FROM Table WHERE id NOT IN(SELECT id FROM Table LIMIT 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

What a good reason to include an auto-incremented primary key in all tables. Then you wouldn't have duplicates.

The simplest way is to use a temporary table:

create temporary table nodups as
    select distinct *
    from table t;

truncate table t;

insert into t
    select *
    from nodups;

Upvotes: 1

Related Questions