1

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

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Sid
  • 2,792
  • 9
  • 55
  • 111
  • Possible duplicate of [Using MySQL: Update field with values using Inner Join](http://stackoverflow.com/questions/14006856/using-mysql-update-field-with-values-using-inner-join) – jpw Sep 07 '16 at 12:37
  • 1
    Also, https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables?noredirect=1&lq=1 which would have been a better dupe target. – jpw Sep 07 '16 at 12:45

2 Answers2

2

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
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • 2
    Exactly like @Arulkumar said. An earlier Post for this is http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables – Nebi Sep 07 '16 at 12:43
  • by this all questions are getting updated for the doctor_id 8. How can we update the single question? @Arulkumar – Sid Sep 07 '16 at 12:49
1

Use the following:

UPDATE question q INNER JOIN answer a on q.answer_id = a.answer_id 
SET q.question = 'dmvvnnv',a.comment = 'covonfvk',a.rating = 5
WHERE a.doctor_id = 8
AT-2017
  • 3,114
  • 3
  • 23
  • 39
  • gives 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 'FROM question q INNER JOIN answer a on q.answer_id = a.answer_id WHERE a.doctor' at line 2 @AT-2016 – Sid Sep 07 '16 at 12:41
  • by this all questions are getting updated for the doctor_id 8. How can we update the single question? @AT-2016 – Sid Sep 07 '16 at 12:51
  • 1
    There is a unique id in the question table. Just use it to update a single row like ques_id - 1, 2, 3, 4, so on. – AT-2017 Sep 07 '16 at 12:52