[Level 2] How to delete duplicate data in MySQL.

drop table if exists abc;
create table abc (i int DEFAULT NULL);
insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL);
-- insert into abc values (),(),(),(); -- work
-- insert into abc values (1),(),(),(),(); -- not work
-- ERROR 1136 (21S01): Column count doesn't match value count at row 2

-- case 1:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
-- case 2:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);

/* result:
mysql> -- case 1:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1' at line 1
mysql> -- case 2:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);
Query OK, 9 rows affected (0.00 sec)
*/


/*
easy way to delete duplicate data.
create table cba as select distinct from abc;
drop table abc;
rename table cba to abc;
*/

Wish this helps.
regards,
Stanley Huang