Danny Guo | 郭亚东

How to Swap Column Values in SQL

 ·  202 words  ·  ~2 minutes to read

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;

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.

← How to Use Git Bisect for Debugging How to Replace All Instances of a Substring in JavaScript →

Follow me on Twitter or Mastodon or subscribe to my newsletter or RSS feed for future posts.

Found an error or typo? Feel free to open a pull request on GitHub.