CODEWITHSUNDEEP
phpmysql
Anish
Anish

Reputation: 4978

How we can update multiple tables in mysql

How can update multiple tables using single query.I have to update two tables at a time. I tried this query but it fails to update

UPDATE  contact,bill SET companyid =2  WHERE  userid=1 AND companyid =20;

Upvotes: 1

Views: 798

Answers (3)

Eddie
Eddie

Reputation: 13116

This should work.

UPDATE contact INNER JOIN bill SET companyid =2 WHERE userid=1 AND companyid =20;

Or to prevent conflicts.( Unsure of your table layout but heres the rough idea )

UPDATE contact x INNER JOIN bill y SET x.companyid =2 WHERE x.userid=1 AND y.companyid =20;

Upvotes: 3

Alex Howansky
Alex Howansky

Reputation: 53533

If the reason you "have to update two tables at a time" is to ensure that the changes don't get half done, then you may want to consider wrapping your queries in a transaction:

START TRANSACTION;
UPDATE ...
UPDATE ...
COMMIT;

(Or whatever the MySQL syntax is...)

Upvotes: 2

CristiC
CristiC

Reputation: 22698

You cannot update in the same time. 

UPDATE contact SET companyid=2 WHERE userid=1 AND companyid =20;
UPDATE bill    SET companyid=2 WHERE userid=1 AND companyid =20;

Upvotes: 0

Related Questions