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