Creating a temporary merge table works fine for me on 5.0. Your table isn't innodb is it? That will fail with an error like you're getting.
Regards, Gavin Towey -----Original Message----- From: Dante Lorenso [mailto:da...@lorenso.com] Sent: Thursday, December 10, 2009 3:20 PM To: mysql@lists.mysql.com Subject: stored procedure and random table name -> temp table, merge, prepared statement All, I have a stored procedure that I'm writing where I need to run a lot of queries against a particular table. The name of the table will be a parameter to the stored procedure ... example: CALL normalize_data('name_of_table_here'); Since I want to run queries against this table, I don't want to have to use prepared statements for all the queries because treating my queries as strings gets ugly. Ideally I want to use the table name as a variable in the stored procedure, but as a hack around that, I thought about trying this trick instead: give the table name an alias. -- remove our temporary table if it already exists DROP TABLE IF EXISTS dante; -- -- clone the table structure CREATE TEMPORARY TABLE dante LIKE name_of_table_here; -- -- change the temporary table to a merge table which references the named table ALTER TABLE dante ENGINE=MERGE UNION(name_of_table_here); Once these 3 statements were run, the "merge" table would essentially just be a view on the underlying table and all my following queries could reference the "dante" table and not the strangely named random table. Note, that queries above that use "name_of_table_here" would need to be prepared and executed using the string concat approach. The problem I am having is that this strategy is not working. After running the statements above, I check my new "dante" table and it doesn't work: DESC dante; Error Code : 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist So, how can I accomplish what I am trying to do? I just want to alias a random table to a fixed name (preferably as a temporary table name so that it won't conflict with other connections running similar code simultaneously) so that I can avoid having to use prepared statements through my whole stored procedure. I may potentially perform 20-30 queries to the table which is passed in and want to keep this code looking clean. I could avoid this problem altogether if I can assign an alias to a table: ALIAS dante TO name_of_table_here; or use a variable table name in a query inside a stored procedure: SET @table_name = 'name_of_table_here'; INSERT INTO some_table (value) SELECT something FROM @table_name WHERE ...; Am using MySQL 5.1.36. Any pointers? -- Dante This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
