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:

x|y
12|5

MySQL

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:

x|y
12|12

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.