Update all rows with same id to same value and update only one of them with specific value-CodePudding

I have 2 tables questions and answers. Each question has 4 answers, One of them is the correct answer:

      Questions
 ------------------- 
|  id  |  question  |
 ------ ------------ 
|  1   |     q1     |
 ------ ------------ 

                     Answers
 ------ ---------- -------------- --------------- 
|  id  |  answer  |  is_correct  |  question_id  |
 ------ ---------- -------------- --------------- 
|  1   |     a1   |       0      |       1       |
 ------ ---------- -------------- --------------- 
|  2   |     a2   |       0      |       1       |
 ------ ---------- -------------- --------------- 
|  3   |     a3   |       1      |       1       |
 ------ ---------- -------------- --------------- 
|  4   |     a4   |       0      |       1       |
 ------ ---------- -------------- ---------------

I'm trying to update the is_correct. So for example, if the correct answer is number 3 and I want to set it to 4, I would have to update the is_correct for number 3 to 0 and then update the is_correct for number 4 to 1.

This could be done with 2 queries:

--First query
UPDATE answers SET is_correct = 0 WHERE question_id = 1

--Second query
UPDATE answers SET is_correct = 1 WHERE id = 4

Is it possible to achieve this with one query or this is the best way?

CodePudding user response：

Tested on postgreSQL, you might need to change some minor details (use CAST function?):

UPDATE answers SET is_correct = (id = 4)::int WHERE question_id = 1

CodePudding user response：

For MySQL the query would be

update answers set is_corrent=(id=4) where question_id=1;

CodePudding user response：

INSERT INTO answers (id, is_correct , question_id ) VALUES (1,0, 1), (2, 0, 1), (3, 0, 1), (4, 1, 1) ON DUPLICATE KEY UPDATE is_correct = VALUES(is_correct)