How to update two rows by one query?

Question

I have a table like this:

// Answers
+----+---------+-----------------+-------------+
| id |  answer | accepted-answer | question_id |
+----+---------+-----------------+-------------+
| 1  | answer1 | null            | 1           |
| 2  | answer2 | 1               | 1           |
| 3  | answer3 | null            | 1           |
+----+---------+-----------------+-------------+

Now I'm trying to change accepted-answer. I want this output:

+----+---------+-----------------+-------------+
| id |  answer | accepted-answer | question_id |
+----+---------+-----------------+-------------+
| 1  | answer1 | null            | 1           |
| 2  | answer2 | null            | 1           |
| 3  | answer3 | 1               | 1           |
+----+---------+-----------------+-------------+

Note: I just have the new id (the id of new accepted answer, not old one) which is $id=3 in the example above. I also have question_id which is $question_id=1 in the example above.

I can do that by two separated queries like these:

UPDATE Answers SET accepted_answer = null WHERE question_id = :question_id;

And

UPDATE Answers SET accepted_answer = 1 WHERE id = :id;

Now I want to know, can I do that in the same time and in one query?

How about `...SET accepted_answer = CASE WHEN question_id ... THEN... WHEN... THEN END` — Strawberry, Apr 20 '16 at 16:12
Control flow is your friend here: http://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else — Sebastianb, Apr 20 '16 at 16:12
One statement here is probably not more efficient than two. Add conditions and accepted_answer is not null, and accepted_answer <> 1 — paparazzo, Apr 20 '16 at 16:15
Keep the `WHERE` from the first query, and change the `SET` to `SET accepted_answer = IF(id <> :id, NULL, 1)` — Uueerdo, Apr 20 '16 at 16:16

How to update two rows by one query?

0 Answers0