From: Joerg Bruehe (joergmysql.com)
Date: Thu Jul 10 2008 - 08:39:59 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi !

Ananda Kumar wrote:
> Hi All,
> I was to rename a table as below
>
> set t_name=now();
> rename table amc to concat('amf_',t_name);
>
> but i am getting below error. How do i fix this.
>
> ERROR 1064 (42000): 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 'concat('amf_',t_name)' at line 1
>
>
> I can do the same using unix script, but just wanted to check if i can do
> this in mysql itself.

SQL (standard) AFAIK demands that all identifiers (names of database,
table, view, column, ...) be constant strings in the statements,
it does not allow expressions for them.

If there are exceptions in MySQL, I am not aware of them.

To do such things as you want, you have to use "dynamic SQL":
Construct the statement as a string (which does not use expressions for
identifiers), and then send it to the server.

A Unix script generating a command file which you pass to the command
line client is one way of doing dynamic SQL.
Any other client application (Perl, PHP, ...) could do the same.

The command line client can do it in SQL, using "prepared statements":
Construct a string with your statement into a variable, then "prepare" a
statement from the variable and "execute" it.
Several tests in the test suite do it, this is how to find them:
    cd mysql-test/t ; grep -i 'prepare.*' *.test
Use them as a model for your needs.

HTH,
Jörg

--
Joerg Bruehe, MySQL Build Team, joergmysql.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]