Mysql – Swap Columns on some rows

MySQL

I have a table of about 106k rows where about 11k rows have the values in 2 columns swapped. I want to run a query to fix it, but I don't think I can do this:

UPDATE `game` 
SET `homescore`=`awayscore`, `awayscore`=`homescore` 
WHERE (`awayscore`>`homescore` AND `winner`=`hometeam`) OR (`awayscore`<`homescore` AND `winner`=`awayteam`);

Or can I? I'm worried the 2 scores will end up the same value.

Also, please validate that my query will do what I intend to do:

I need to swap the home and away scores on the rows where the recorded winner (which is correct) doesn't match the scores recorded (they were accidentally swapped by a coding mistake now fixed) saying who the winner is (team with more points).

Best Answer

Your concerns are valid , mysql documentations says

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value: UPDATE t1 SET col1 = col1 + 1;

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;

To work it around, I'd suggest adding 2 columns, say homescore_old and awayscore_old, copying homescore and awayscore there, and then do you UPDATE using newly added columns on the right side assignment. When it's done, you can drop old columns. It might also be possible to use just one extra column, but again, documentation states,

Single-table UPDATE assignments are generally evaluated from left to right.

and in my understanding, "generally" is not the same as "always".

Related Question