How can I alter multiple tables at once in mysql?

mysql alter
John Doe picture John Doe · Jan 20, 2012 · Viewed 28.7k times · Source

I am trying to alter multiple tables and change the size of the username VARCHAR column to 999 as its current size is too small and now things are screwed up. How can I do this?

I have tried the following and it worked for one table but when trying to update multiple table names it returned errors:

ALTER TABLE  `TABLE_NAME` CHANGE `username` VARCHAR( 999 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Answer

Mchl picture Mchl · Jan 20, 2012

You can't do it with a single query. You need to query information_schema views to get the list of tables and columns to change. You will then use the resulting resultset to create ALTER queries (either in an external application/script or within MySQL using cursors and prepared statements)

Related questions

How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the …

Read More
How to add new column to MYSQL table?

I am trying to add a new column to my MYSQL table using PHP. I am unsure how to alter my table so that the new column is created. In my assessment table I have: assessmentid | q1 | q2 | q3 | q4 | …

Read More
How to move columns in a MySQL table?

Currently I am having the following MySQL table: Employees (empID, empName, department); I want to change the table to the following: Employees (empID, department, empName); How can this be done using ALTER statements? Note: I want to change only column …

Read More