Updating Multiple tables using a single query

[sngumo]

Hi

 

I wanted to ask whether there is a single query which can be used to update multiple tables with multiple values

 

Code currently being used

***********************

$recipient_query = mysql_query("UPDATE secondary_table SET amount = '$newsec_amt' WHERE membersemail = '$recipient'");

$sender_query = mysql_query("UPDATE primary_table SET amount = '$newsender_amt' WHERE membersemail = '$sender'");

***********************

 

Thanks

[andy_b42]

You should be able to do this by terminating each query with a ";"

 

eg

$query = "
   UPDATE secondary_table SET amount = '$newsec_amt' WHERE membersemail = '$recipient';
   UPDATE primary_table SET amount = '$newsender_amt' WHERE membersemail = '$sender';";

mysql_query($query);

 

This will execute both queries.

 

If what you want to do is join both queries into a single string, you can do this, but i would recommend against it because it will confuse things quite significantly. You would need to specify which table each field is on (primary_table.field etc) and you would still have to specify that you were changing each individual field on each table. You can't do something like:

 

"update table1, table2 set field1 = 'value' where table1.field2 = x and table2.field = y"

 

because the sql server wont know which tables you are referring to.

[sngumo]

Thanks for that, this coding business is still a bit green to me.

[Daniel0]

You should be able to do this by terminating each query with a ";"

 

eg

$query = "
   UPDATE secondary_table SET amount = '$newsec_amt' WHERE membersemail = '$recipient';
   UPDATE primary_table SET amount = '$newsender_amt' WHERE membersemail = '$sender';";

mysql_query($query);

 

This will execute both queries.

 

From the manual:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

[angelcool]

I agree with Daniel0, andy_b42's  suggestion is kinda funky;

 

I use mysqli_multi_query (http://www.php.net/manual/en/mysqli.multi-query.php); I just love it , it even allows you to use mysql user variables (programming within MySQL queries).

 

I do not know if plain mysql (as oppose to msyqli) library supports multi-queries;

 

Cheers

[Daniel0]

Also, you might want to look into transactions so you can roll back if either of the queries fail.

[angelcool]

...mmm, interesting very very interesting.

 

I think I have already heard about MySQL transaction but never went into details, but thanks to my friend google I found (rather

than just going through MySQL gigantic documentation  [i guess just lazy to]):

 

http://www.tutorialspoint.com/mysql/mysql-transactions.htm

 

Good reference,

Thank you.

[Daniel0]

Just remember that MyISAM is non-transactional. You'll have to use InnoDB.