How to Swap Column Values in SQL
source link: https://www.dannyguo.com/blog/how-to-swap-column-values-in-sql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
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.
You can encrypt messsages with my GPG key if you'd like.
© 2021 Danny Guo
This content is licensed under CC BY-NC-SA 4.0 and hosted on GitHub.
As an Amazon Associate I earn from qualifying purchases.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK