[TriLUG] mysql syntax for ambiguous file name, please

[Jeremy Portzer]

Brian McCullough wrote:
> On Sat, Jun 16, 2007 at 05:32:16PM -0400, Brian McCullough wrote:
>> On Sat, Jun 16, 2007 at 05:05:28PM -0400, Michael Rulison wrote:
>>> I want to do the following query, but it fails:
>>>
>>> alter table jos_* rename to j_fca_*;
>>>
>>> but the '*' is not accepted (inside, outside of quotes (double or single);
>> The wild card match character in SQL is "%".  
> 
>> Most RDBMS ( database engines ) do not allow wild cards in Data
>> Definition statements.
> 
> 
> Found what I was looking for.  The syntax for the ALTER TABLE and RENAME
> TABLE statements are very explicit about having single table names on
> both the left and right hand sides of those statements.
> 
> 
> About the only way that I can see to do what you are trying would be to
> use SELECT to create a list of the table names that you wanted to
> change, in a text file.  

In a text file!   I know I'm really late in replying to this, but this 
is an SQL database... who needs text files!

You can create such a list within temporary storage within MySQL itself, 
  likely as part of a stored routine(or ad-hoc stored routine code). 
I'm mostly an Oracle person myself, but I know this is possible in 
MySQL.  Basically you will need to build the DDL (data definition 
language) statements at runtime and then run an "exec" type of statement 
  to execute these, once they are built.  In pseudo-code:

* Run sql statement of form:  select table_name from data_dictionary 
where table_name like 'jos_*'  [whatever the right MySQL syntax is]
...* iterate over results of select statement, table_name in $i
...* define variable $j with regular expression on $i to create new 
table name j_fca_*
...* build sql statement in a string value, e.g.:  "alter table $i 
rename to $j;"
...* execute statement with "exec" type statement
* end loop

Then you could edit that list by creating a
> RENAME TABLE statement out of the list, adding the changed names.  I
> would then submit that new statement to MySQL for execution.
> 

Yep, that's the right process... but without TEXT FILES when you've got 
a database!  :-)

--Jeremy