RENAME TABLE with CONCAT string fails
I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 11:00 -0400 5/28/05, Jason Dixon wrote: On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Perl. Construct the table identifier and place the result into your SQL statement, then execute the statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 17:50 +0200 5/28/05, [EMAIL PROTECTED] wrote:
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
Ah, yes. This'll work. I forgot about prepared statements. :-)
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
