How to delete duplicated columns in mysql table

Question

I want to delete duplicate columns in mysql. Here is sample:

person------city

Tom--------NY
Tom--------NY
Jhon-------LA

Desired output like this

person------city

Tom--------NY
Jhon-------LA

How can we do this in MySQL?

Answer 1

If you do not have primary key in your table, then it is easier to create new table with distinct values and then insert back into original table (assuming your table name is "ORIGINAL_TABLE" ) :

Create table Table2 as Select distinct person, city from ORIGINAL_TABLE;

truncate table ORIGINAL_TABLE;

insert into ORIGINAL_TABLE (person, city ) Select person, city from Table2 

drop table Table2;