Mysql：更新全部或首次选择和更新后？

Question

I have a table with 2 column: id and count. Count is exclusive OR (only one of them can be 1 at same time). I need to update a row with count = 1 and need to put the previous count 1 at 0.

I think two strategies:

1) Do a select on the table, find the row at 1. Update it at 0. Update the row that i want be at 1.

2) Global update the table at count 0. Update the row that i want be at 1.

In the first case i do 3 query but "small". In the second case i do 2 query but one of them is "big".

Which strategy is better?

Answer 1

If you are simply swapping the value on two rows you could do it in a single query.

eg.

+----+-------+
| id | count |
+----+-------+
| 1  | 0     |
| 2  | 0     |
| 3  | 1     |
+----+-------+

Update using a self join:

update myTable a, myTable b set
    a.`count` = b.`count`,
    b.`count` = a.`count`
where a.id = '2'
and b.id = '3'

The result:

+----+-------+
| id | count |
+----+-------+
| 1  | 0     |
| 2  | 1     |
| 3  | 0     |
+----+-------+

If you want to target a specific row, you can do that also. eg. row with id 1 should have a count of 1, and whatever currently has a count of 1 should be reset back to 0.

update myTable a, myTable b set
    a.`count` = 1,
    b.`count` = 0
where a.id = '1'
and b.`count` = '1'