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.

Check this : [Swap Tables Columns values - SQL - arpit](https://stackoverflow.com/questions/4198587/how-do-i-swap-column-values-in-sql-server-2008/44300052#44300052) — Arpit Trivedi, Jun 01 '17 at 06:44

eggyal · Accepted Answer · 2015-03-06T11:13:22.760

9

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

edited Mar 06 '15 at 11:13

answered Mar 06 '15 at 11:08

eggyal

122,705
18
212
237

Nice idea using a `JOIN`. – axiac Mar 06 '15 at 11:33
NOTE: This works only when the table has unique ID's (as in the example) otherwise there is no way to self-join the table – Efrain Plaza Mar 26 '21 at 19:56
@EfrainPlaza: if you have tables without primary keys, you’ve got bigger problems than this. – eggyal Mar 26 '21 at 19:57

axiac · Answer 2 · 2015-03-06T11:42:57.177

A little bit of math magic can help:

UPDATE myTable
SET start_year = start_year - end_year,
    end_year   = end_year + start_year,
    start_year = end_year - start_year
WHERE start_year > end_year
  AND end_year IS NOT NULL

Remark

I cannot tell how reliable is this method of updating. In order to have the desired effect, the expressions must be computed and the assignments must be done in the order presented in the query. If MySQL decides to compute or assign them in a different order then the query will produce a mess in the table.

An excerpt from the manual:

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

It is not very clear from the fragment above if the assignments are evaluated from left to right or not. It says "generally" but it doesn't explain what are the exceptions.

I tested the query on MySQL 5.5.24 using the small sample data provided in the question and it worked as expected.

Advice

Backup your table before running this query. You have been warned!

@eggyal you are right. I prefer to make it explicit because it is specified in the question. It doesn't affect the speed or the outcome. — axiac, Mar 06 '15 at 11:48

jarlh · Answer 3 · 2017-11-03T09:13:43.473

-2

Warning, the general ISO/ANSI SQL answer below will not work on MySQL:

update tablename
  set start_year = end_year,
      end_year = start_year
where start_year > end_year

edited Nov 03 '17 at 09:13

answered Mar 06 '15 at 11:07

jarlh

42,561
8
45
63

2

No. MySQL is non-standard in that it will use the *updated* value of `start_year` when updating `end_year`. – eggyal Mar 06 '15 at 11:10
Since this answer is still downvoted I suppose I could delete it. However, I'll keep it as a warning! – jarlh Nov 03 '17 at 09:12

MySQL - Swap values of two columns in a row

3 Answers3

Remark

Advice