distal-attribute
distal-attribute
distal-attribute
distal-attribute

update values in specific databases

User, date Message
Written by thalaki
2 years 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
2 years ago
5009 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
2 years 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
2 years ago
5009 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.