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
…
Read the rest of this article »
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
