Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer

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 ;
share|improve this answer
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.

share|improve this answer
2  
Can you elaborate on this code or offer some explanation on how this code answers this question? –  bluefeet May 13 '13 at 23:01
    
Sorry, I had seen the question but I had not much time to explain the response. –  Lito May 14 '13 at 18:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.