Swapping column values in MySQL

by

I just had to deal with the same and I’ll summarize my findings.

  1. The UPDATE table SET X=Y, Y=X approach obviously doesn’t work, as it’ll just set both values to Y.

  2. Here’s a method that uses a temporary variable. Thanks to Antony from the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ 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;

  3. This method was offered by Dipin in, yet again, the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/. 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_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

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.

This is my test schema:

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

More Related Contents:

  1. Reorder / reset auto increment primary key
  2. MySQL Trigger after update only if row has changed
  3. Implementing Comments and Likes in database
  4. How to check if mysql database exists
  5. JOIN queries vs multiple queries
  6. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  7. Storing Data in MySQL as JSON
  8. Difference Between Schema / Database in MySQL
  9. How do I add a custom CHECK constraint on a MySQL table?
  10. MySQL: Cloning a MySQL database on the same MySql instance
  11. How to change the default port of mysql from 3306 to 3360
  12. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  13. Implementing a hierarchical data structure in a database
  14. Does MySQL allows to create database with dot?
  15. Commit data in a mysql container
  16. Laravel join with 3 Tables
  17. Difference between two table structure
  18. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  19. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  20. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  21. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  22. Find date range overlaps within the same table, for specific user MySQL
  23. Rename a table in MySQL
  24. Where does MySQL store database files on Windows and what are the names of the files?
  25. Should I normalize my DB or not?
  26. How to reuse auto_increment values? [duplicate]
  27. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  28. How to Get True Size of MySQL Database?
  29. Put MySQL database under version control?
  30. MySQL: Computed Column

Leave a Comment