SQL tricks
September 5, 2022

How to remove duplicates from one column table?

Let we have some one table contains duplicates values:

create table tbl ( id int);

insert into tbl values (1), (10), (10), (20), (2), (2), (10), (20);

select * from tbl;
/* 
+====+
| id |
+====+
| 1  |
+----+
| 10 |
+----+
| 10 |
+----+
| 20 |
+----+
| 2  |
+----+
| 2  |
+----+
| 10 |
+----+
| 20 |
+----+
*/

The simplest way to remove duplicates is create new table by select distinct values, add primary key for prevent future duplicates and replace old table by new

create table tbl_uniq as select distinct id from tbl;
alter table tbl_uniq add primary key (id);

drop table tbl;
rename table tbl_uniq to tbl;

select * from tbl;
Result:
+====+
| id |
+====+
| 1  |
+----+
| 2  |
+----+
| 10 |
+----+
| 20 |
+----+

Test on SQL online editor: https://sqlize.online/sql/mysql80/0e44dc64c027b33a96d7270ce1843d36/