update two tables at once

Question

I'm using 5.1.41-3ubuntu12.10 for my MySQL version.

UPDATE profiledata SET aboutyou = '$aboutyou', quotes = '$quotes' WHERE uid = '$sess_uid'

and

UPDATE profileprivacy SET aboutyouPrivacy = '$aboutyouPrivacy', quotesPrivacy = '$quotesPrivacy' WHERE uid='$sess_uid'

$sess_uid is the same for both. I was wondering if I could combine both MySQL queries into one mysql_query.

I was thinking it would be something like SET profiledata.aboutyou = 'Just a developer.', not really sure.

At best you could use mysqli::multi_query php.net/manual/en/mysqli.multi-query.php to send a single command to the server versus two separate calls, assuming your PHP install supports mysqli. — onteria_
– onteria_, Commented May 25, 2011 at 17:58

Johan · Accepted Answer · 2011-05-25 18:52:20Z

You can use a join like this:

$query = "UPDATE profiledata t1 JOIN profileprivacy t2 ON (t1.uid = t2.uid) SET t1.aboutyou = '$aboutyou', t1.quotes = '$quotes', t2.aboutyouPrivacy = '$aboutyouPrivacy', t2.quotesPrivacy = '$quotesPrivacy' WHERE t1.uid = '$sess_uid'";

Johan · Accepted Answer · 2011-05-25 18:52:53Z

MySQL does have multi-table update support: http://dev.mysql.com/doc/refman/5.0/en/update.html.

UPDATE profiledata, profileprivacy SET aboutYou = ..., aboutyouPrivacy = ... WHERE (profiledata.uid = $sess_uid) OR (aboutyouPrivacy.uid = $sess_uid)

or something similar should do the trick.

Collectives™ on Stack Overflow

update two tables at once

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related