How to delete duplicate records mysql

Question

i have table

DATE       TRAN_VAL     NAME
may01         24           hari
may01         24           hari
jun22         29           giri
jun22         29           giri
dec19         33           niraj
jan01         56           balu

in above table the names hari&giri having complete duplicate records, How to remove/delete them O/P

DATE       TRAN_VAL     NAME
may01         24           hari
jun22         29           giri
dec19         33           niraj
jan01         56           balu

Possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) ... Please spend a few minutes searching Stack Overflow and Google before you post a question like this. — Tim Biegeleisen, Oct 28 '15 at 06:12

score 0 · Answer 1 · answered Oct 28 '15 at 06:26

0

If you have primary key in your table then you can by this query-

If want to keep only latest record-

DELETE t1 FROM mytable t1 
JOIN mytable t2 ON t1.name = t2.name 
AND t1.primary_key < t2.primary_key;

If want to keep only first record record-

DELETE t1 FROM mytable t1 
JOIN mytable t2 ON t1.name = t2.name 
AND t1.primary_key > t2.primary_key;

If you don't have primary key and any field on which basis you can distinguish your row then you can achieve it by alter query-

ALTER IGNORE TABLE mytable ADD UNIQUE Uk_name(name);

Note: It will lock the table, so avoid on production server in peak time.

If you don't want this unique key then remove it after removing duplicate.

answered Oct 28 '15 at 06:26

Zafar Malik

6,734
2
19
30

alter ignore table test1 add unique(DATE,TRAN_VAL, NAME); – suri5913 Oct 28 '15 at 06:44
if you want your record unique based on combination of these 3 fields then your alter query is fine. – Zafar Malik Oct 28 '15 at 06:45
execute following statement "select version();" and update – Zafar Malik Oct 28 '15 at 09:26

score 0 · Answer 2 · answered Oct 28 '15 at 07:17

DELETE FROM table_name where DATE,TRAN_VAL,NAME in( SELECT DATE,TRAN_VAL,NAME FROM ( select DATE,TRAN_VAL,NAME FROM table_name GROUP BY DATE,TRAN_VAL,NAME having COUNT(*)>1)temp );

How to delete duplicate records mysql

2 Answers2