How to Swap Column Values in SQL
If you need to swap column values in SQL, it's easy to do in most databases. The
big exception is MySQL. For Postgres, Oracle, SQL Server, and SQLite,
you can simply set the columns equal to each other in an
update. Here's an
example that you can try with SQLite. You can also try it online with this DB
Fiddle for SQLite.
create table coordinates ( x integer, y integer ); insert into coordinates (x, y) values (5, 12); select * from coordinates;
You should see this output (after turning headers on):
sqlite> .headers on sqlite> select * from coordinates; x|y 5|12
And then if you run:
update coordinates set x = y, y = x; select * from coordinates;
The values should be swapped:
Unfortunately, this approach doesn't work for MySQL. You'll end up with both columns having the same value. The output of the update will be:
You can try it for yourself with this DB Fiddle for MySQL.
Artem Russakovskii gives us a few workarounds in this post. See this Stack Overflow answer for more discussion.
The cleanest workaround is to use a temporary variable:
update coordinates set x = (@temp := x), x = y, y = @temp;
You can try it for yourself with this DB Fiddle.