CODEWITHSUNDEEP
mysqlsql
IceFire
IceFire

Reputation: 4137

RENAME table if target table does not exist

I work with a legacy system and there are multiple batches that are executed after one another. In each of these batches there should be a rename of a table, although this rename should only happen once.

So, there could be:

RENAME TABLE oldName TO newName

in batch1 and batch2. However, in batch2 it would be best if this statement could just be ignored. Of course, it would be more clean to just memorize if the statement has been executed but this clean solution is near to impossible in the current code base.

So, I would like a MySQL solution that basically says: If table newName does not exist, then execute the rename command. Otherwise, do nothing and also do not send an error message.

Is this possible? If yes, how?

Upvotes: 1

Views: 3133

Answers (1)

Barry
Barry

Reputation: 3318

This has been answered...

Mysql: RENAME TABLE IF EXISTS

With the following code... (all credit to original author)

SELECT Count(*)
INTO @exists
FROM information_schema.tables 
WHERE table_schema = [DATABASE_NAME]
AND table_type = 'BASE TABLE'
AND table_name = 'oldName';
SET @query = If(@exists=0,'RENAME TABLE oldName TO newName','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: 1

Related Questions