CODEWITHSUNDEEP
mysqlalter-table
techtheatre
techtheatre

Reputation: 6140

Change multiple field types simultaneously in MySQL

I have a table (it was imported from Excel) that has a wide variety of automatically identified/assigned types (int, float, decimal, etc). There are about 160 columns...and i want to standardize them. I know that to change the field type i use ALTER TABLE and the CHANGE command...but you have to specify the column name...and i really don't want to have to do this individually for every column. I tried something like this:

ALTER TABLE `Table_Name` CHANGE * * DECIMAL( 7, 4 ) NULL DEFAULT NULL

but that did not work. To make matters slightly more complicated...there is actually one field which is the primary key StateName that is not numeric (the values are like TX,VA,FL,NY)...and do i need to leave it alone.

In short, i am looking for something like:

ALTER TABLE `Table_Name` CHANGE * * DECIMAL( 7, 4 ) NULL DEFAULT NULL EXCEPT WHERE Column='StateName'

I just made up that syntax...i know it is nowhere close.

Can this be done?

Upvotes: 1

Views: 1232

Answers (2)

pim
pim

Reputation: 12577

The pure SQL answer to this would be:

ALTER TABLE [table_name]
MODIFY [col1] float,
MODIFY [col2] varchar(255) null

and so on...

Upvotes: 0

techtheatre
techtheatre

Reputation: 6140

So...based on the "answer" from OMG Ponies and assuming that there really is not a way to do this within MySQL...i wrote the following PHP script which accomplished the complete job in less than 1 second... 

$sql = "SHOW COLUMNS FROM `Table_Name`";
$result = @mysql_query($sql, $db);
while($row = mysql_fetch_array($result)){
    $ColumnName = $row[0];
    if($ColumnName!='StateName'){
        $UpdateSql = "ALTER TABLE `Table_Name` CHANGE `$ColumnName` `$ColumnName` DECIMAL( 7, 4 ) NULL DEFAULT NULL ";
        @mysql_query($UpdateSql, $db);
    }
}

Hopefully it helps someone else someday.

Upvotes: 2

Related Questions