Swap Columns on some rows?

Question

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).

Answer 1

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".

Answer 2

In any decent RDBMS (e.g Postgres, SQL-Server, Oracle, DB2), the simple approach would work fine and swap the values of the two columns:

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

In MySQL, it will not work as expected! You'll have to use some kind of trick, like this one:

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

You could also add one or two more columns in the table, use them for swapping and then drop them but it won't be very efficient. A better approach would be to use another (temporary) table:

CREATE TABLE temp_swap
( pk INT NOT NULL         -- whatever datatype the Primary Key of your table is
, homescore INT           -- INT or FLOAT or DECIMAL
, awayscore INT           -- whatever the two columns are
, PRIMARY KEY (pk)
) ;

Copy the values to be swapped there:

INSERT INTO temp_swap
  (pk, homescore, awayscore)
SELECT pk, homescore, awayscore 
FROM game 
WHERE (awayscore > homescore AND winner = hometeam) 
   OR (awayscore < homescore AND winner = awayteam) ;

And then back, swapped, into the table:

UPDATE game AS g
  JOIN temp_swap AS t
    ON t.pk = g.pk
SET g.homescore = t.awayscore, 
    g.awayscore = t.homescore ;

DROP TABLE temp_swap ;

Answer 3

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

First assignment homescore = (@temp:=homescore) store the original homescore value into variable @temp, now you can invert the values setting homescore as awayscore and awayscore with the value stored in @temp.