MySQL Query to update multiple tables

Question

I have a Quiz module in which I have two tables quiz_question and quiz_options. quiz_question is saving Questions and quiz_option saving options for particular questions.

table structure for quiz_option: id | question_id | text | is_correct

table structure for quiz_question id | title | desctiption |

Where question_id is foreign key to id of quiz_question

I want to write a query to update quiz_question and all its corresponding quiz_options is a single query.

Why do you want to update them in a single query? I don't think it's possible. You can use transactions if you want to make sure the updates are atomic. — jussius, Oct 10 '16 at 12:55

score 0 · Answer 1 · answered Oct 10 '16 at 12:55

Multiple table update you can use foreign key connect two and more table with foreign key concept update parent table primary key to child table foreign key see link http://www.hostingadvice.com/how-to/mysql-foreign-key-example/.

table quiz_question PK (question_id)
table quiz_option FK (question_id)

VishalParkash · Answer 2 · 2016-10-10T13:14:35.930

0

Get the id of the question and Update them one by one as you cannot update two tables using a single query.

$ID_VALUE = mysqli_real_escape_string($conn, $_GET['id']);
UPDATE quiz_option SET fields_name = 'value' WHERE question_id = $ID_VALUE;
UPDATE quiz_question SET fields_name = 'value' WHERE id = $ID_VALUE;

edited Oct 10 '16 at 13:14

answered Oct 10 '16 at 13:09

VishalParkash

490
3
15

1

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) Please do not teach bad coding habits. – Jay Blanchard Oct 17 '16 at 13:09
He/she seems like a beginner to me so helped him/her with easy answer. – VishalParkash Oct 17 '16 at 13:14
Alright.. My mistake once again. :) I will careful next time – VishalParkash Oct 17 '16 at 13:18

score 0 · Answer 3 · edited Oct 10 '16 at 19:33

0

Here is the query:

INSERT INTO quiz_question(title,description) VALUES ('test','test question'); 
INSERT INTO quiz_option(question_id,text,is_correct) VALUES (LAST_INSERT_ID(),'test','0'),(LAST_INSERT_ID(),'test2','0'),(LAST_INSERT_ID(),'test3','0'),(LAST_INSERT_ID(),'test4','1')

edited Oct 10 '16 at 19:33

answered Oct 10 '16 at 18:13

Avinash Singh

1

This is Insert Query I need to update table.Anyways I think it is not possible with single Query. – Kritika Shrivastava Oct 11 '16 at 06:15

score -1 · Answer 4 · answered Oct 10 '16 at 12:54

-1

try this

UPDATE  table1 a
        INNER JOIN table2 b
            ON a.ID = b.ID
SET     a.value = b.value

answered Oct 10 '16 at 12:54

mhmd

254
2
3
14

1

@ArditMeti the [documentation](http://dev.mysql.com/doc/refman/5.5/en/update.html) disagrees – apokryfos Oct 10 '16 at 12:57
1

@ArditMeti http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query – mhmd Oct 10 '16 at 12:58

MySQL Query to update multiple tables

4 Answers4