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` http://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_, May 25 '11 at 17:58

score 8 · Accepted Answer · edited May 25 '11 at 18:52

8

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'";

edited May 25 '11 at 18:52

Johan

74,508
24
191
319

answered May 25 '11 at 18:02

Adrian B

1,490
1
19
31

score 1 · Answer 2 · edited May 25 '11 at 18:52

1

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.

edited May 25 '11 at 18:52

Johan

74,508
24
191
319

answered May 25 '11 at 17:58

Marc B

356,200
43
426
500

update two tables at once

2 Answers2

Linked