Updated: August 8th, 2010
Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.
Here are my findings:
The
UPDATE swap_test SET x=y, y=x;
approach doesn't work, as it'll just set both values to y.
PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference]Here's a method that uses a temporary variable. Thanks to Antony from the comments for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values
…
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who is currently pursuing his own projects and regularly enjoys hacking Android, PHP, CSS, Javascript, AJAX, Perl, and regular expressions, working on Wordpress plugins and tools, tweaking MySQL queries and server settings, administering Linux machines, blogging, learning new things, and other geeky stuff.
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
