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/