mysql - sql query to replace city pune to mumbai and mumbai to pune in the table

Question

i have one table emp with column name city

city                         output table
-----
mumbai                           pune
pune                             mumbai
mumbai                           pune
pune                             mumbai
pune                             mumbai

i want update query to replace pune to mumbai and mumbai to pune

Answer 1

You can do this with a query:

select (case when city = 'mumbai' then 'pune'
             when city = 'pune' then 'mumbai'
             else city
        end)
. . .

If you want to change the values, then:

update table t
     set city = (case when city = 'mumbai' then 'pune'
                      when city = 'pune' then 'mumbai'
                 end)
     where city in ('mumbai', 'pune');