How to delete the duplicate record from table in mysql

Question

I want to delete duplicate records from table. The table does not have primary key (ID), so answers like this are not suitable.

Here is my try:

DELETE FROM afscp_permit
USING afscp_permit, afscp_permit AS vtable
WHERE (NOT afscp_permit.field_name=vtable.field_name)
AND (afscp_permit.field_name=vtable.field_name)

duplicate value of single column called field_name, if it is alreday existed then delete that row — chaya, Sep 16 '14 at 15:22
@d'alar'cop, nothing happens, condition is never true and is therefore meaningless. — Basilevs, Sep 16 '14 at 15:44
possible duplicate of [how to delete duplicates on mysql table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table) — AbsoluteƵERØ, Sep 16 '14 at 15:56
Better duplicate is http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Basilevs, Sep 16 '14 at 16:09

score 0 · Answer 1 · answered Sep 16 '14 at 16:11

0

Following might help if there is no bug in your DBMS and no constraints are in the way:

ALTER IGNORE TABLE afscp_permit ADD UNIQUE INDEX field_name_index (field_name );

answered Sep 16 '14 at 16:11

Basilevs

20,335
15
54
98

score 0 · Answer 2 · answered Sep 17 '14 at 17:19

0

How about creating a temporary table with the same columns and doing:

INSERT INTO temp SELECT DISTINCT * FROM afscp_permit;
DELETE FROM afscp_permit;
INSERT INTO afscp_permit SELECT * FROM temp;
DROP TABLE temp;

answered Sep 17 '14 at 17:19

David Lopez

313
3
12

How to delete the duplicate record from table in mysql

2 Answers2