Swapping column values in MySQL

Question

I have a MySQL table with coordinates, the column names are X and Y. Now I want to swap the column values in this table, so that X becomes Y and Y becomes X. The most apparent solution would be renaming the columns, but I don't want to make structure changes since I don't necessarily have permissions to do that.

Is this possible to do with UPDATE in some way? UPDATE table SET X=Y, Y=X obviously won't do what I want.

---

Edit: Please note that my restriction on permissions, mentioned above, effectively prevents the use of ALTER TABLE or other commands that change the table/database structure. Renaming columns or adding new ones are unfortunately not options.

Answer 1

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