CODEWITHSUNDEEP
mysqlduplicates
Bhushan Ahire
Bhushan Ahire

Reputation: 89

remove one duplicate value from two columns in mysql

I have the table as following

| country | state |
--------------------
| USA     | MH    |
| USA     | PB    |
| USA     | KN    |
| USA     | TN    |
| USA     | CG    |
| USA     | MH    |

I have now two values of 

| country | state |
--------------------
| USA     | MH    |
| USA     | PB    |

and i just want to remove single 

| country | state |
--------------------
| USA     | MH    |

so my table will look like

| country | state |
--------------------
| USA     | MH    |
| USA     | PB    |
| USA     | KN    |
| USA     | TN    |
| USA     | CG    |

please help me with this

thank you in advanced

Upvotes: 1

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Actually removing the duplicates from the table is a bit difficult because the table does not have a primary key. One method is to copy the data to a new table and re-insert it:

create temporary table temp_t as
    select distinct country, state
    from t;

truncate table t;

insert into t(country, state)
    select country, state
    from temp_t;

Upvotes: 1

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

select distinct country,state from table1 T1

Upvotes: 2

Related Questions