Swapping column values in MySQL table

in Database, mysql / No Comments

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

Swapping column values in MySQL table by Anil Kumar Panigrahi

demo link for Swapping column values in MySQL table

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

With more than 9 years experience in PHP and Founder of Anil Labs, a blog for PHP and related posts. contributed posts regarding CodeIgniter, CakePHP and Learn PHP online ...Contact : [email protected]

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>