Renaming Database Tables Error in SQL

Question

I'm trying to rename my database tables using the answer given here:

How To have Dynamic SQL in MySQL Stored Procedure

but I'm getting an sql error when I attempt to run the following in phpMyAdmin SQL tab:

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT 'RENAME TABLE ', 
             GROUP_CONCAT('', TABLE_SCHEMA, ''.'', TABLE_NAME,
             ' TO ', TABLE_SCHEMA, ''='.osx_''', TABLE_NAME, '')) AS q
             FROM 
             information_schema.Tables WHERE TABLE_SCHEMA='my_database'';;'
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
delimiter ;

The error is as follows:

#1064 - You have an error in your SQL syntax;  
check the manual that corresponds to your MySQL  
server version for the right syntax to use near  
'RENAME TABLE ', GROUP_CONCAT('', TABLE_SCHEMA, ''.'', TABLE_NAME,' at line 3

I'm not seeing the error, can anyone spot it please?

--EDIT--

After reviewing Max's answer I reformatted the query using backticks:

CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = SELECT
             CONCAT('RENAME TABLE ', 
             GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, 
             '` TO `', TABLE_SCHEMA, '`.`osx_', TABLE_NAME, '`')) AS q
             FROM 
             `information_schema`.`Tables` WHERE TABLE_SCHEMA=`my_database`;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

from another example I found (see below), and I'm still getting sql errors:

#1064 - You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right 
syntax to use near 'SELECT
             CONCAT('RENAME TABLE ', 
             GROUP_CONCAT('`', TABLE_SCHEMA, '`.`',' at line 3

I also tried the other example:

SELECT 
    CONCAT('RENAME TABLE ', 
    GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, 
    '` TO `', TABLE_SCHEMA, '`.`osx_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='my_database';

This executes but produces the following warning:

This table does not contain a unique column. 
Grid edit, checkbox, Edit, Copy and Delete features are not available.

and doesn't do anything to the db.

HELP!

do you have access to command line via ssh? – ravnur Dec 30 '13 at 07:38 — ravnur, Dec 30 '13 at 07:38

score 0 · Answer 1 · answered Dec 30 '13 at 00:06

0

'SELECT 'RENAME

You have opened parameter for CONCAT() with a single quote, and when MySQL sees another one it expects next parameter.
You can either escape quotes or use double quotes for string literals.

CONCAT('SELECT "RENAME TABLE ", GROUP_CONCAT( ...

answered Dec 30 '13 at 00:06

Max Tsepkov

466
6
15

I've updated my question with some additional code. Thanks for your help @Max Tsepkov – secondman Dec 30 '13 at 05:45
This is phpmyadmin specific. Try using mysql command line interface. – Max Tsepkov Dec 30 '13 at 11:40
I won't always have command line available in every situation so I need a solution that will work in phpMyAdmin. – secondman Jan 01 '14 at 11:39

Renaming Database Tables Error in SQL

1 Answers1