Mysql is mostly used for all website made in PHP because of its so many good advantages. I am also working in PHP MYSQL from more than 6 years. I am PHP Freelancer India and I am available to hire. Now in this article I am going to explain you how we can swap values of two columns of a table in mysql database.

For example we have 2 columns ‘state_id’ and ‘district_id’ in a table called ‘state_and_district’, So while inserting data into this table by mistake state id inserted into district_id and District id inserted into state_id, So instead of changing each row values we can do this by using a simple query of mysql.

We cant use a query like assign state_id = district_id and district_id = state_id because it will just set both value to district_id. Instead of this we will use temporary variable to swap column values. So below is query which can be used to swap values of 2 columns in table and will work for “IS NOT NULL”

UPDATE swap_test SET state_id=district_id, district_id=@temp WHERE (@temp:=state_id) IS NOT NULL;

Above query is using temporary variable to swap values of column values and It is working properly. Although there is second query also available which is perfect way to do this and will work for NOT NULL and NULL both values.

UPDATE swap_test SET state_id=(@temp:=state_id), state_id = district_id, district_id = @temp;

Above query is perfect query to swap two column values with each other in Db table. I am sure there will be lots of other ways to do this and if you know any other better way then let us know in comments.

I hope this article help someone for sure. I am PHP Freelancer India and I am having more than 6 years of experience. So contact me if you have any work.