MySQL - Update table values from another table

Question

I have two tables - subjects and questions.

Structure and rows of table subjects are like :-

----------------------------
subject_id | subject_name
----------------------------
1          | physics
2          | chemistry
3          | biology

Structure and rows of table questions are like :-

question_id | subject_id | subject_name | question
---------------------------------------------------------
1           | 0          | physics      | demo_question_1
2           | 0          | physics      | demo_question_2
3           | 0          | chemistry    | demo_question_3
4           | 0          | biology      | demo_question_4

I added column subject_id in questions table after I had already inserted some rows. I want to bulk update subject_id of questions table as per subjects table. I can update them individually, using WHERE, but I was hoping if any single query would do this work?

Answer 1

Not sure what you mean by a single query versus a WHERE, but I think this will suit your needs.

UPDATE questions q, subjects s 
SET q.subject_id = s.subject_id 
WHERE q.subject_name = s.subject_name;