30

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 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.

Some references:

● ● ●

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). 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.



Share
  • Ikai Lan

    I can haz performance metrics plz>?

  • http://beerpla.net Artem Russakovskii

    @Ikai
    The query doesn't use a temp table or filesort, which are 2 of the biggest performance hogs.
    It performed really well on about 70k items that I ran it on and the only performance hit is the usual join penalty and larger memory footprint.
    However, it does the job well, and I'm willing to accept such a penalty until another working solution appears.

  • Antony Curtis

    Hi,

    This appears to work fine for me:
    UPDATE swap_test SET x = y, y = @temp WHERE (@temp := x) IS NOT NULL;

    Regards,

    • http://beerpla.net Artem Russakovskii

      @Antony
      Verified as working. Thank you. The IS NOT NULL tweak does the job, though I'm not 100% sure why.

  • Antony Curtis

    The IS NOT NULL is a complex operator which the optimizer doesn't strip out and replace with a constant.

  • Antony Curtis

    As an aside…

    A better alternative would be to rename the columns to swap them. If the table definition is not changed by the operation, then it will be fast and efficient.

    Unfortunately, MySQL's fallback method when it can't simply rename the columns involves a CREATE TEMP / INSERT .. SELECT / DROP / RENAME behind the scenes which would be worse than iterating through all the rows with an UPDATE operation.

    From the MySQL manual:

    Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:
    Renaming a column or index.
    Changing the default value of a column.
    Changing the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values.
    In some cases, an operation such as changing a VARCHAR(10) column to VARCHAR(15) may be immediate, but this depends on the storage engine for the table. A change such as VARCHAR(10) to a length greater than 255 is not immediate because data values must be modified from using one byte to store the length to using two bytes.

    YMMV.

  • http://openquery.com.au Arjen Lentz

    Hmm, generally this kind of situation occurs with a flawed schema design?

  • http://beerpla.net Artem Russakovskii

    I just noticed one horrible, subtle thing:

    UPDATE swap_test SET x=y, y=@temp WHERE @temp:=x IS NOT NULL;

    vs

    UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

    In the first query, the missing parentheses will cause data corruption. I will make sure to point it out and correct my example.

  • http://beerpla.net Artem Russakovskii

    @Arjen
    What kind of situation and what is the flaw in the schema? Can you provide more details please?

  • http://beerpla.net Artem Russakovskii

    @Antony
    Thanks for all the hints.
    I definitely didn't want to go the table rename approach, as MySQL is indeed quite dumb at making the slightest changes to the schema (even the .frm hack could be adopted by the server natively, why in the world hasn't that happened yet?).

    In my particular case, I only wanted to modify a very small subset of the data.

  • http://rpbouman.blogspot.com/ Roland Bouman

    Hi!

    "In the first query, the missing parentheses will cause data corruption. I will make sure to point it out and correct my example."

    I think it's a matter of operator precedence. Look here: http://dev.mysql.com/doc/refman/5.1/en/operator-precedence.html

    As you can see, the := binds weakest so omitting the parenthesis will cause

    x IS NOT NULL

    to be evaluated first. That expression evaluates to a boolean value, but MySQL doesn't have those so it becomes either 1 (TRUE) or 0 (FALSE)

    As for the obvious first query,

    UPDATE swap_test SET x=y, y=x;

    do you notice any difference between InnoDB and MyISAM? Personally I think this query should just work but I am aware of the fact that MySQL takes a few shortcuts in the UPDATE implementation.

  • http://beerpla.net Artem Russakovskii

    @Roland
    Yeah, that makes sense, and I thought the caveat was worth pointing out, seeing how some examples on the web are omitting parentheses.

    As far as the first query, I just tested both MyISAM and InnoDB and they both behave the same – x and y end up equal, as expected. The MySQL manual confirms it: "Single-table UPDATE assignments are generally evaluated from left to right."

  • Pabloj

    All major databases "think is sets" and make the swap seamless, if I remember well just MySQL and Firebird don't work this way.

  • Dmitri Mikhailov

    I would not recommend to use this approach, as the update fails to swap values if one of them is null:

    drop table if exists swap_test_1
    ;
    create table swap_test_1
    (
    id int not null auto_increment
    , x varchar(255) null
    , y varchar(255) null
    , primary key (id)
    ) engine=innodb;

    insert into swap_test_1 values
    ('1', 'a', '10')
    , ('2', 'b', null)
    , ('3', 'c', '30')
    ;

    update swap_test_1 set x=y, y=@temp where (@temp:=x) is not null;

    select * from swap_test_1;

  • Dmitri Mikhailov

    *** use this record set:

    insert into swap_test_1 values
    ('1', 'a', '10')
    , ('2', null, '20')
    , ('3', 'c', '30')
    ;

  • http://beerpla.net Artem Russakovskii

    @Dmitri
    You're absolutely right. I had it in the back of my mind to test the NULL case but it escaped me till now. So, approach #2 doesn't swap the values if one of them is NULL. Approach #3 still works fine. I'll make a note of it, until a new approach is developed for #2.

  • Dipin Hora

    update swap_test_1 set x=(@temp:=x), x = y, y = @temp;

    Works for all scenarios based on my quick tests.

  • http://beerpla.net Artem Russakovskii

    @Dipin
    Brilliant. That indeed works for all test cases. I think this is finally the cleanest and most elegant solution.

  • http://blogs.mysql.com/peterg Peter Gulutzan
  • custom software developer

    Thanks for that helpful post. that was awesome ;)I shall necessarily try

  • Mathijs

    maybe you can do something with this aswell :P
    had some problems with switching integer values in 2 different rows with eachother.
    they are used to describe an order, and eacht time there will be a difference of one.
    this is what did the trick.

    UPDATE tablename AS first, tablename AS second, tablename AS third
    SET first.ord = second.ord, second.ord = third.ord
    WHERE first.parent = second.parent AND first.id = 1 AND first.id = third.id AND second.ord = third.ord-1 AND first.status > 0 AND second.status > 0

  • Handy

    I've used
    ('1', 'a', '10')
    , ('2', null, '20')
    , ('3', 'c', '30')
    It's great ! :)

  • Dogo

    Hi,

    i looked around a lot but do not seem to get it right:
    I'd like to swap two values in different lines. As entry id 14 (e.g) has "order" 5 and another has "order" 6.
    Is there any way to swap these two within one single query? I already have the id (14) and order of the first one.

    I ran through different approaches like "ORDER BY order DESC LIMIT 1" but it always takes two queries…

    Best Regards,

    Dogo

  • http://beerpla.net Artem Russakovskii

    @Dogo
    Try posting this at http://www.stackoverflow.com. Should have a discussion going in no titme.

  • http://www.microshell.com Maresa

    @Dogo: Swapping values between 2 rows from the same table is explained on my post here: http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

    In a sense, it's basically the same solution as this post. You do self join on the table, then swap columns.

  • Alex

    What about swapping the id column of table 1, with another column from table 2?

    Can I apply any of these methods to swap columns across tables?

  • ali

    I don't know if any person post this approach already:

    UPDATE tblone t1, tblone t2
    SET
    t1.Column=(@temp:=t1.Column),
    t1.Column = t2.Column ,
    t2.Column = @temp
    WHERE
    t1.ID=10 and t2.ID=20

  • Dude

    In SQLserver x=y, y=x just works o_O

  • Jackson

    Simply change the clolumn name..:)

  • Gauthier

    Thanks ! It works like a charm