MySQL - Swap values of two columns in a row

Question

I'm trying to correct some data in a table which stores two years:

id | start_year | end_year
---|------------|---------
1  |    2001    |   2003
2  |    2008    |   2005
3  |    2004    |   2010
4  |    2012    |   NULL
5  |    2003    |   2004

Like in row 2 the years are the wrong way round. How can I swap the values of those columns, on rows where start_year > end_year?

Note: row 4 should not be swapped, where end_year is NULL. That should remain the same.

Answer 1

As stated in the MySQL manual:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Thus you cannot use a simple UPDATE command. Instead, you can perform a self-join:

UPDATE myTable old
  JOIN myTable new USING (id)
SET    new.start_year = old.end_year,
       new.end_year = old.start_year
WHERE  old.start_year > old.end_year