Sid Sid - 3 months ago 9
MySQL Question

Updating two tables with an inner join

I have two tables one is question and other is answer.

Question table has fields as

question_id, question, type, answer_id.


Answer table has fields as

answer_id, question_id, comment, rating, doctor_id


Now I want to update the answer belongs to the question where doctor_id. For this I tried to write this query :

update question q
set q.question = 'dmvvnnv',a.comment = 'covonfvk',a.rating = 5
INNER JOIN answer a on q.answer_id = a.answer_id
WHERE a.doctor_id = 8


But it is giving me the syntax error :


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'INNER JOIN answer a on q.answer_id = a.answer_id WHERE
a.doctor_id = 8' at line 1

Answer

For MySQL UPDATE with JOIN syntax is different, the SET part should come after the JOIN

Use the following query to update the entries:

UPDATE question q
INNER JOIN answer a ON a.answer_id = q.answer_id
SET q.question = 'dmvvnnv'
    ,a.comment = 'covonfvk'
    ,a.rating = 5
WHERE a.doctor_id = 8