update values in specific databases

User, date Message
Written by thalaki
3 months ago
Category: Running SQL scripts
2 posts since Tue, 26 Feb 13 		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?
Written by ansgar
3 months ago
3950 posts since Fri, 07 Apr 06

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...)
Written by thalaki
3 months ago
2 posts since Tue, 26 Feb 13 		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>
Written by ansgar
3 months ago
3950 posts since Fri, 07 Apr 06

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE
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.