CODEWITHSUNDEEP
mysqlsql
mm1234
mm1234

Reputation: 23

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

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?

Upvotes: 0

Views: 867

Answers (3)

Felipe Sanchez
Felipe Sanchez

Reputation: 59

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

Upvotes: 0

Asgar
Asgar

Reputation: 2422

For MySQL the query would be

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

Upvotes: 1

Aconcagua
Aconcagua

Reputation: 25516

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

Upvotes: 0

Related Questions