ALTER TABLE requires a table name for the current database.
You can specify:
ALTER TABLE db_name.tbl_name MODIFY col_name ...
or
mysql> alter table test1.t1 modify test1.t1.set1 varchar(30);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
but you cannot modify a table in a different database to the
one you first specify after TABLE db_name:
mysql> alter table test1.t1 modify test2.t1.set1 varchar(30);
ERROR 1102 (42000): Incorrect database name 'test2'
.
.
.
mysql> show create table t1 \G
*************************** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` set('this','is','today') default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql> alter table t1 modify set1 varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` varchar(30) default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
It should not take you too long to use the command history
to recall the alter table ... statement, edit it, and work your
way through the databases like that.
Another way is to write a script that will parse the
database names in the data directory, and then generate the
SQL code to perfom the multiple ALTER TABLE statements.
The script could then replace the db_name to be changed on
each iteration.
To be safe you could make copies of your databases, and
perform the ALTER TABLE statements away from your live data
directory.
When you are happy with the modifications, then copy the
altered databases back to your live data directory.
Personally I'd feel alot safer altering one database table
at a time - just in case errors start appearing.
You need to be carefull that you do not loose any multiple
values in your set, as varchar will only hold one value at
a time.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Thu, 30 Mar 2006, Jason Dimberg wrote:
> To: mysql@lists.mysql.com
> From: Jason Dimberg <[EMAIL PROTECTED]>
> Subject: AlterTable Structure Across Multiple DBs
>
> I have about 25 databases with the same structure and
> occasionally need to update the table structure. For
> example, I recently found a mistake in a field that was of
> type SET and needed to be VARCHAR. I will now need to
> edit each table. Is there an easy method to alter table
> structure across multiple dbs as opposed to editing each
> one individually?
>
> In retrospect I should have combined them into one db and
> may consider doing that.
>
> All dbs start with 'pm_' and have identically named tables
>
> MySQL 5.0.18
> Windows 2003
>
> Thank you,
> --
>
> *Jason Dimberg*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
