CODEWITHSUNDEEP
mysqlsqldatabasesql-update
Sitansu
Sitansu

Reputation: 3329

How to update common field length in table for MYSQL database for all tables?

I am facing issue to update two fields (Say Active, Version) length for all tables which contains this two field(Say Active, Version) in our Database.

Is there any way to update two fields.

Is it possible in MySQL?

If possible please share me.

After Google it I found this Mysql- Single Sequence Table for all Tables

Thanks

Sitansu

Upvotes: 0

Views: 312

Answers (1)

Paul Campbell
Paul Campbell

Reputation: 1986

The short answer is 'Yes' with a series of ALTER TABLE statements.

However, first, backup your database.

Next, satisfy yourself that you have identified all the tables where instances of these columns exist by querying the information_schema. Something like

SELECT `table_name`, `column_name`, `column_type`, `column_default`, `is_nullable`
FROM `information_schema`.`columns`
WHERE `table_schema` = 'yourdbname'
AND `column_name` IN('Version','Say Active')
ORDER BY `column_name`;

This will get you a list of the tables and allow you to see if the current definitions are consistent.

Next, you need to create some ALTER TABLE statements for each of the tables and columns identified to give the FULL definition for the columns you are changing. If you are not comfortable with the mysql syntax get the current definitions with 

SHOW CREATE TABLE `tablename1`;

on one of the affected tables and copy them out of that, substituting the column length in the edited version.

Use the MODIFY COLUMN rather than CHANGE COLUMN syntax since you are not going to change the column names.

If we assume that your definition for the Version column came back as

Version varchar(12) NOT NULL

and you want to double its length, you would create an ALTER TABLE statement for each instance: 

ALTER TABLE `tablename1` MODIFY COLUMN `Version` VARCHAR(24) NOT NULL; 
ALTER TABLE `tablename2` MODIFY COLUMN `Version` VARCHAR(24) NOT NULL;

etc.

When you have updated all the tables you can rerun the query against information_schema to check that both columns have been MODIFIED in all tables.

Upvotes: 1

Related Questions