Danny Guo | 郭亚东

How to Swap Column Values in SQL

  ·  217 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;
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.


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

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

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


comments powered by Disqus