Updated: September 16th, 2012
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:
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