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

Two alternatives 1. Use a temporary table 2. Investigate the XOR algorithm

Answer 2

UPDATE table SET X=Y, Y=X will do precisely what you want. The values are taken from the old row and assigned to a new copy of the same row, then the old row is replaced. You do not have to resort to using a temporary table, a temporary column, or other swap tricks.

@D4V360: I see. That is shocking and unexpected. I use PostgreSQL and my answer works correctly there (I tried it). See the PostgreSQL UPDATE docs (under Parameters, expression), where it mentions that expressions on the right hand side of SET clauses explicitly use the old values of columns. I see that the corresponding MySQL UPDATE docs contain the statement "Single-table UPDATE assignments are generally evaluated from left to right" which implies the behaviour you describe.

Good to know.

Answer 3

Ok, so just for fun, you could do this! (assuming you're swapping string values)

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 6    | 1    | 
| 5    | 2    | 
| 4    | 3    | 
+------+------+
3 rows in set (0.00 sec)

mysql> update swapper set 
    -> foo = concat(foo, "###", bar),
    -> bar = replace(foo, concat("###", bar), ""),
    -> foo = replace(foo, concat(bar, "###"), "");

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 1    | 6    | 
| 2    | 5    | 
| 3    | 4    | 
+------+------+
3 rows in set (0.00 sec)

A nice bit of fun abusing the left-to-right evaluation process in MySQL.

Alternatively, just use XOR if they're numbers. You mentioned coordinates, so do you have lovely integer values, or complex strings?

Edit: The XOR stuff works like this by the way:

update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;

Answer 4

Assuming you have signed integers in your columns, you may need to use CAST(a ^ b AS SIGNED), since the result of the ^ operator is an unsigned 64-bit integer in MySQL.

In case it helps anyone, here's the method I used to swap the same column between two given rows:

SELECT BIT_XOR(foo) FROM table WHERE key = $1 OR key = $2

UPDATE table SET foo = CAST(foo ^ $3 AS SIGNED) WHERE key = $1 OR key = $2

where $1 and $2 are the keys of two rows and $3 is the result of the first query.

Answer 5

I've not tried it but

UPDATE tbl SET @temp=X, X=Y, Y=@temp

Might do it.

Mark

Answer 6

This surely works! I've just needed it to swap Euro and SKK price columns. :)

UPDATE tbl SET X=Y, Y=@temp where @temp:=X;

The above will not work (ERROR 1064 (42000): You have an error in your SQL syntax; ...)

Thorn

Answer 7

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

Answer 8

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

Works for all scenarios in my quick testing.

Answer 9

You could change column names, but this is more of a hack. But be cautious of any indexes that may be on these columns