Mysql: How to swap data of one column between rows?

Question

I already read Switch id numbers of two rows in MySql and Mysql: Swap data for different rows, yet I don't understand. Suppose a table colors that looks like this:

------------------------------------------
| id | box | pouch  | color    | value   |
-----------------------------------------
| 1  |  1  |   1    | red      | 30      |
| 2  |  1  |   1    | blue     | 20      |
| 3  |  1  |   1    | green    | 10      |
| 4  |  1  |   1    | yellow   | 40      |
| 5  |  1  |   1    | purple   | 20      |
| 6  |  1  |   1    | black    | 50      |
| 7  |  1  |   2    | red      | 30      |
| 8  |  1  |   2    | blue     | 20      |
| 9  |  1  |   2    | green    | 10      |
| 10 |  1  |   2    | yellow   | 40      |
| 11 |  1  |   2    | purple   | 20      |
| 12 |  1  |   2    | black    | 50      |
| 13 |  2  |   1    | red      | 35      |
| 14 |  2  |   1    | blue     | 25      |
| 15 |  2  |   1    | green    | 15      |
| 16 |  2  |   1    | yellow   | 45      |
| 17 |  2  |   1    | purple   | 25      |
| 18 |  2  |   1    | black    | 55      |
| 19 |  2  |   2    | red      | 35      |
| 20 |  2  |   2    | blue     | 25      |
| 21 |  2  |   2    | green    | 15      |
| 22 |  2  |   2    | yellow   | 45      |
| 23 |  2  |   2    | purple   | 25      |
| 24 |  2  |   2    | black    | 55      |
------------------------------------------

How can I swap the "value" of a row with another, leaving the "id", "box", "pouch", and "color" intact?

Notes: 1. I need to swap the value of box='1' with box='2' 2. The number of rows are dynamic

Example:

SWAP ROWS OF VALUE WITH BOX='1' WITH ROWS OF VALUE WITH BOX='2'

Result:

------------------------------------------
| id | box | pouch  | color    | value   |
-----------------------------------------
| 1  |  1  |   1    | red      | 35      |
| 2  |  1  |   1    | blue     | 25      |
| 3  |  1  |   1    | green    | 15      |
| 4  |  1  |   1    | yellow   | 45      |
| 5  |  1  |   1    | purple   | 25      |
| 6  |  1  |   1    | black    | 55      |
| 7  |  1  |   2    | red      | 35      |
| 8  |  1  |   2    | blue     | 25      |
| 9  |  1  |   2    | green    | 15      |
| 10 |  1  |   2    | yellow   | 45      |
| 11 |  1  |   2    | purple   | 25      |
| 12 |  1  |   2    | black    | 55      |
| 13 |  2  |   1    | red      | 30      |
| 14 |  2  |   1    | blue     | 20      |
| 15 |  2  |   1    | green    | 10      |
| 16 |  2  |   1    | yellow   | 40      |
| 17 |  2  |   1    | purple   | 20      |
| 18 |  2  |   1    | black    | 50      |
| 19 |  2  |   2    | red      | 30      |
| 20 |  2  |   2    | blue     | 20      |
| 21 |  2  |   2    | green    | 10      |
| 22 |  2  |   2    | yellow   | 40      |
| 23 |  2  |   2    | purple   | 20      |
| 24 |  2  |   2    | black    | 50      |
------------------------------------------

Anyone got some ideas? Thanks

Answer 1

To do the swap, try resetting one of them to a temporary value. You need to do that, otherwise the second update will affect the first one.

UPDATE mytable SET box = -1 WHERE box = 1; -- Set box 1 to a temporary value
UPDATE mytable SET box = 1 WHERE box = 2; -- Set box 2 to box 1
UPDATE mytable SET box = 2 WHERE box = -1; -- Set box 1 to box 2

I've assumed it is not possible to have a negative box number. If -1 is permissible, use something else, such as null.

For extra safety, wrap the above in a transaction, so if anything goes wrong, you can rollback.

Answer 2

Create a table that holds all values you want to swap.

 create table tmp_t as select * from t;

Then update your table twice:

update t t1 
inner join tmp_t t2 on t1.color = t2.color 
                    and t1.pouch = t2.pouch 
                    and t1.box = 1 and t2.box = 2 
set t1.value = t2.value;

update t t1 
inner join tmp_t t2 on t1.color = t2.color 
                    and t1.pouch = t2.pouch 
                    and t1.box = 2 and t2.box = 1 
set t1.value = t2.value;

And that's it. See it working live in an sqlfiddle.