MYSQL to swap 2 columns

In MYSQL swaping 2 column values on same time can done by the following way. For example create a table swap_test as follows,

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Insert some values on the ‘swap_test’ table as seen below,

INSERT INTO `swap_test` VALUES (‘1’, ‘a’, ’10’);
INSERT INTO `swap_test` VALUES (‘2′, NULL, ’20’);
INSERT INTO `swap_test` VALUES (‘3’, ‘c’, NULL);

Now here we are going to interchange the field value ‘X’ to ‘Y’ and ‘Y’ to ‘X’ on the same time by running following query,

UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: