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
  • 277,661
  • 50
  • 283
  • 320
Sid
  • 2,660
  • 6
  • 50
  • 102
  • 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,541
  • 13
  • 48
  • 65
  • 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,036
  • 3
  • 19
  • 36
  • 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