Swap Columns on some rows? 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`&gt;`homescore` AND `winner`=`hometeam`) OR (`awayscore`&lt;`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).
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 &gt; homescore AND winner = hometeam) 
   OR (awayscore &lt; 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 &gt; homescore AND winner = hometeam) 
   OR (awayscore &lt; 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 &gt; homescore AND winner = hometeam) 
   OR (awayscore &lt; 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 ;