Swapping column values in MySQL table

In this post I would like to share my views on how swapping column values in MySQL table. Recently I have a requirement that one record should be active mode in the given point of time remaining records should be in inactive mode. I have got the solution for it. So I would like to share my views on it.

Swapping column values in MySQL table by Anil Kumar Panigrahi

MySQL table :

CREATE TABLE IF NOT EXISTS users (
  id int(11) NOT NULL AUTO_INCREMENT,
  status varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

Sample data to the table :

INSERT INTO users (id, status) VALUES
(1, 'inactive'),
(2, 'inactive'),
(3, 'inactive'),
(4, 'inactive'),
(5, 'active');

here we have one active user , remaining are in inactive status. Just I want to active the user id ‘2’ then run the below query:

MySQL Query :

update users a
 inner join users b on a.id <> b.id
    set a.status = b.status
 where a.id in (2,5) and b.id in (2,5);

here swapped column values between 2, 5

Also see

Anil Labs -> MySQL

How to generate Data dictionary in MySql

How to import and export big database files