32

Swapping Column Values in MySQL


Posted by Artem Russakovskii on February 17th, 2009 in Databases, Linux, MySQL, Programming

Updated: September 16th, 2012

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 swap<em>test s1, swap</em>test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;
    1
    2
    3
    
    mysql&amp;gt; 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.

Some references:

● ● ●
Artem Russakovskii is a San Francisco programmer and blogger. Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.