Menu
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
987
In MYSQL, we can update the multiple tables in a single UPDATE query. In the below query, both 'order' and 'order_detail' tables are updated at once.
USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`. `", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "your_schema_goes_here"; Then you can run the output from this to do what you need. Save this answer.
From multiple tables To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.
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)
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
