update values in specific databases

thalaki posted 5 years ago in Running SQL scripts
hi, I want to create a script which will update a table in a number of databases under the same host, all databases have the same prefix.

I am not able to figure out the syntax of it though, can you help me out?
ansgar posted 5 years ago
UPDATE db1.yourtable SET col=x WHERE id=y;
UPDATE db2.yourtable SET col=x WHERE id=y;

(I'm nearly sure I misunderstood something here...)
thalaki posted 5 years ago
Thanks for the reply, here are some things i did not clarify:

The number of databases is unknown and the script should be able to handle that (to retrieve the databases and update each one)

Also the suffix of the table is not known, only the prefix is known, so the tables could be named like prefix_<something>
ansgar posted 5 years ago
FROM information_schema.TABLES
TABLE_NAME LIKE 'prefix_%'

Of course you will need some PHP or Perl or whatever to execute that SQL query, and afterwards fire one UPDATE for each result.

Please login to leave a reply, or register at first.