Move multiple tables with prefix to archive database with prepared statement, why is this MySQL statement invalid?

Question

I am attempting to move multiple tables from one database to another and archive them, but the below statement fails with a syntax error on the select with:

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 '@tbls ENGINE=archive RENAME @tbls.database_two' at line 1

The select statement is identical to the syntax suggested in the manual and it doesn't seem as if I'm running into a capitalization error either.

What am I missing?

SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (
    SELECT GROUP_CONCAT(TABLE_NAME)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'database_one'
    AND TABLE_NAME LIKE 'prefix_%'
);
SET @rnStmt = CONCAT('ALTER TABLE @tbls ENGINE=archive RENAME @tbls.database_two');
-- SELECT @rnStmt;
PREPARE stmt FROM @rnStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MySQL 5.1 CentOS 6.5

edit: copied wrong error msg

UPDATE: Made some progress, the select works and the sytax check but I'm getting 'No Database Selected' on the prepare statement.

SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (
    SELECT GROUP_CONCAT(TABLE_NAME)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'database_one'
    AND TABLE_NAME LIKE 'prefix_%'
);
SET @rnStmt = CONCAT('ALTER TABLE ', @tbls, ' ENGINE=archive RENAME database_two.', @tbls);
PREPARE stmt FROM @rnStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I've tried USE and a few other variations, but no luck.

Answer 1

For multiple tables you need to use RENAME TABLE statement, and also, you need to collect full current table names and full new table names, then construct the result query and execute it -

SELECT
  GROUP_CONCAT(CONCAT('database_one.',TABLE_NAME)),
  GROUP_CONCAT(CONCAT('database_two.',TABLE_NAME))
INTO
  @from, @to
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = 'database_one' AND TABLE_NAME LIKE 'prefix_%';

SET @rnStmt = CONCAT('RENAME TABLE ', @from, ' TO ', @to);

PREPARE stmt FROM @rnStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;