Mysql – How to create one update query (thesql) on multiple databases

databaseMySQLqueryupdate

I have several DBS on the same mysql server.
The DBS's structure and schema are exactly the same (only data is different)

the query on certain db works well like this (written in phpmyadmin)

UPDATE  `mdl_modules` SET `visible`=0 
WHERE `name` IN ("survey","feedback","audio","testing")

I thought it could be something like that:

UPDATE `db_name1`.`mdl_modules`, `db_name2`.`mdl_modules`,`db_name3`.`mdl_modules`
SET `visible`=0  WHERE `name` 
IN ("survey","feedback","audio","testing")

but this doesn't work and I get an error
of course I have more that 3 dbs..

Thanks

Best Answer

Use transactions.

START TRANSACTION;
  UPDATE  `db_name1`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
  UPDATE  `db_name2`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
  UPDATE  `db_name3`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
COMMIT;
Related Topic