As such one has to use some imagination and an old technique to remove duplicates. The process is as follows:
- Create a similar table to the original one which contains the duplicate values.
- Insert distinct values in the new table.
- Drop the old table.
- Rename the new table as the old one.
Pretty clear, no? In SQL we can implement the first two steps using the CREATE TABLE ... SELECT syntax. Therefore to remove duplicates from t1, you can execute the following code:
CREATE TABLE t2 SELECT DISTINCT name, surname, country_id FROM t1; DROP TABLE t1; ALTER TABLE t2 RENAME t1;