Jump to content

Updating Multiple tables using a single query


sngumo

Recommended Posts

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

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.

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.

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 :P, 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

...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  :D [i guess just lazy to]):

 

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

 

Good reference,

Thank you.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.