CODEWITHSUNDEEP
mysql
Somebody
Somebody

Reputation: 9645

Mysql: RENAME TABLE IF EXISTS

This DROP TABLE IF EXISTS works, too bad that RENAME TABLE IF EXISTS doesn't work.

Can anyone suggest a solution for this query?

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 'IF EXISTS video_top_day TO video_top_day_for_delete' at line 1

query:

RENAME TABLE IF EXISTS video_top_day TO video_top_day_for_delete

Upvotes: 26

Views: 48232

Answers (9)

borgius
borgius

Reputation: 21

DELIMITER ;;
DROP PROCEDURE IF EXISTS renameTableIfExists;;
CREATE PROCEDURE renameTableIfExists(IN tbl VARCHAR(255), IN toTbl VARCHAR(255))
BEGIN
SELECT DATABASE() INTO @db_name;
SELECT Count(*) INTO @exists FROM information_schema.tables 
  WHERE table_schema = @db_name
    AND table_type = 'BASE TABLE'
    AND TABLE_NAME = tbl;

IF @exists > 0 THEN
   SET @dropToTbl = CONCAT("DROP TABLE IF EXISTS ", toTbl); PREPARE dropToTbl FROM @dropToTbl; EXECUTE dropToTbl;
   SET @renameTbl = CONCAT("RENAME TABLE ", tbl, " to ",  toTbl); PREPARE renameTbl FROM @renameTbl; EXECUTE renameTbl;
   SELECT @tbl, @toTbl;
ELSE   
   SELECT 'Table not exists';
END IF;
END;;
DELIMITER ;

CALL renameTableIfExists('Tags', 'Tags2');

Upvotes: 0

ktaria
ktaria

Reputation: 461

a workaround :

CREATE TABLE IF NOT EXISTS video_top_day;
RENAME TABLE video_top_day TO video_top_day_for_delete

it creates an empty table if not exists !

Upvotes: 0

Karol S
Karol S

Reputation: 9457

Eight years after your question, MariaDB added the exact syntax you want in version 10.5.2: https://mariadb.com/kb/en/rename-table/

IF EXISTS
MariaDB starting with 10.5.2
If this is directive is used, one will not get an error if the table to be renamed doesn't exist.

Upvotes: 7

Damonio
Damonio

Reputation: 606

If you don't want to keep variables you can do also this:

DELIMITER $$
IF EXISTS (SELECT *
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = database()
                     AND  TABLE_NAME = 'oldname')
THEN
    RENAME TABLE oldname to newname;
END if;
$$
DELIMITER ;

You can even further create a function if you want to reuse it

Upvotes: 1

Schiavini
Schiavini

Reputation: 2939

I've managed to execute a code that always works and generates no errors when the table doesn't exist:

SELECT Count(*)
INTO @exists
FROM information_schema.tables 
WHERE table_schema = [DATABASE_NAME]
    AND table_type = 'BASE TABLE'
    AND table_name = 'video_top_day';

SET @query = If(@exists>0,
    'RENAME TABLE video_top_day TO video_top_day_for_delete',
    'SELECT \'nothing to rename\' status');

PREPARE stmt FROM @query;

EXECUTE stmt;

When you don't want to replace [DATABASE NAME] manually you can use the following variable

SELECT DATABASE() INTO @db_name FROM DUAL;

Upvotes: 35

Martin.
Martin.

Reputation: 10539

There's no official solution yet. There has been feature request submitted in 2004, never closed

Upvotes: 13

rkosegi
rkosegi

Reputation: 14678

If you are familiar with PL/SQL, then you can check for existence of table by querying information_schema.columns and based on this perform rename

Upvotes: 0

vulkanino
vulkanino

Reputation: 9134

First create table IF NOT EXISTS. Then RENAME it, so it will always exist!

Otherwise, rename the table, and if it doesn't exist just handle the error.

It is obvious, but it works.

Upvotes: 14

triclosan
triclosan

Reputation: 5724

create table table2 like table1;
insert into table2 select * from table1;
drop table table1;

Upvotes: 2

Related Questions