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 ;
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');
(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);
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
