Swapping Column Values in MySQL


Updated: August 8th, 2010

Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.

Here are my findings:

  1. The
    UPDATE swap_test SET x=y, y=x;

    approach doesn’t work, as it’ll just set both values to y.

    PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference]
  2. Here’s a method that uses a temporary variable. Thanks to Antony from the comments for the “IS NOT NULL” tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn’t swap the values if one of them is NULL. Use method #3 that doesn’t have this limitation.
    UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;
    The parentheses around @temp:=x are critical. Omitting them will cause data corruption.
    1
    2
    3
    mysql> UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;
    Query OK, 3 rows affected
    Rows matched: 3  Changed: 3  Warnings: 0
  3. This method was offered by Dipin in the comments. I think it’s the most elegant and clean solution. It works with both NULL and non-NULL values.
    UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;
  4. Another approach I came up with that seems to work:
    UPDATE swaptest s1, swaptest s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;
    1
    2
    3
    mysql> update swap_test s1, swap_test s2 set s1.x=s1.y, s1.y=s2.x where s1.id=s2.id;
    Query OK, 3 rows affected
    Rows matched: 3  Changed: 3  Warnings: 0

Essentially, the 1st table is the one getting updated and the 2nd one is used to pull the old data from.

Note that this approach requires a primary key to be present.

Test schema used:

1
2
3
4
5
6
7
8
9
10
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 INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);

Do you have a better approach? If so, please share in the comments.

Copy from http://beerpla.net

Advertisements

One comment on “Swapping Column Values in MySQL

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