How to create one update query (mysql) on multiple databases

Question

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

score 2 · Accepted Answer · answered Mar 27 '12 at 08:17

2

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;

answered Mar 27 '12 at 08:17

Janne Pikkarainen

31,852
4
58
81

Is there Any way to do it in One sentence? – Miki A Mar 27 '12 at 09:03

score 0 · Answer 2 · answered Apr 14 '23 at 04:37

Here's a solution I came up with for this using bash. Its assumes that all the database names are prefixed with db_ like in your example. Useful if there are lots of duplicate databases and you don't want to define update queries in a transaction for each one.

mysql -N -B -e "show databases like 'db_%'" \
| while read line; \
do mysql -B -N \
-e "UPDATE mdl_modules SET visible=0 WHERE name IN ("survey","feedback","audio","testing");" \
$line; done

How to create one update query (mysql) on multiple databases

2 Answers2