🔒 Locked

SQL Gurus -- Query help

Started by WebsiteWill Jul 5, 2004 at 11:17 AM 4 replies 750+ views

Original Post

Jul 5, 2004 at 11:17 AM

Is there a way to perform an update across all tables in a database at the same time? What I have are some tables where I was careful to keep the columns named identical. For instance, my Client table has ClientID as a PK and all other tables that will have a foreign key in the Client also have a ClientID column. However, the types on these columns are not all the same thanks to a fluke either on my part or on MySQLCC. Some of the colums that I am certain I assigned as mediumint or shortint seem to be in varchar, tinyint and in some cases tinytext (shrug). I know for a fact that this wasn't intentional and am pretty sure it's due to a scrolling error and they way MySQLCC uses listboxes for data types (easy to mess up with those). At any rate. Would there be a way to update the data types across all of my tables that share the same colum name? I really don't want to spend days sorting out all of these types again. Thanks, Webby

ApochPiQ

1,378

ApochPiQ

Jul 5, 2004 at 12:17 PM

Sure, just list out the table names separated by commas. The MySQL docs on UPDATE should get you started.

[edit] Oops... didn't read the question carefully enough. As mentioned ALTER TABLE is what you're after.

[Edited by - ApochPiQ on July 5, 2004 1:17:02 PM]

Big B

Jul 5, 2004 at 12:31 PM

I think what your after is ALTER TABLE if you want to change the data type of a column. Its also covered in the MySQL docs.

Alex

Jul 5, 2004 at 12:46 PM

You can use the query:

SHOW TABLES FROM database_name;

to get a list of the tables in your database. Then loop through each of the results returned, if you're using PHP, use the following syntax:

$sql_tables = mysql_query("SHOW TABLES FROM database_name");while ($table = mysql_fetch_array($sql_tables)){   // Code here}

Then use ALTER TABLE on each of the tables you get in the array. The syntax for ALTER TABLE can be found here: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

Since I don't know specifically what you want to change in each table.

WebsiteWill

Author

WebsiteWill

Jul 5, 2004 at 12:57 PM

I need all columns that are FKs into another table to take on the data type used by the PK they will be referencing.

This way I can go in and change my ClientID to a mediumint (or whatever) then run a query (or code) that will scan the rest of my tables and find all of the columns with the name ClientID and change it's type to match that of the PK.

Alternatively just do this for all columns with the same name. This way I can go in and place my foreign keys without having to tediously evaluate every column to make sure it has the correct type.

I'm not using PHP. Was hoping to get this done with a plain SQL query. I can use VB to do this if it must be done through code.

Thanks,
Webby

Alex

Jul 7, 2004 at 7:59 AM

You might be able to do this with nested queries. I've never used them myself, so I don't know exactly how they work... But you might be able to use the SHOW TABLES command to list the tables, and nest that within the ALTER TABLE command or something.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.