MySQL Bugs: #4754: 'if exists' syntax for rename table

Bug #4754	'if exists' syntax for rename table
Submitted:	26 Jul 2004 16:38	Modified:	25 Dec 2005 12:34
Reporter:	Karel Vervaeke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	4.0.20	OS:	Linux (Gentoo linux)
Assigned to:		CPU Architecture:	Any

Description:
There doesn't seem to be an 'if exists' on the rename table statement.  This could come in handy for migration scripts (to prevent errors in case they get executed twice).

My migration script has e.g.
rename table old_table name to new_table_name

on first execution, the table is renamed
on second execution this gives
ERROR 1050: Table 'fem' already exists
(note: this is completely as expected)

My problem has two sides:
1) an 'if exists' statement doesn't exist for the rename table command
2) what if new_table_name already exists? We could drop the existing table and actually perform the rename.

How to repeat:
execute the same "rename table old_table_name to new_table_name" statement more than once

Suggested fix:
proposed syntax for the first problem:
rename [if exists] old_table_name to new_table_name

no suggestion for the 2nd.

Thank you for a reasonable feature request. Yes, to be consistent within its own extensions of SQL, MySQL should support IF EXISTS clause in RENAME statetement.

As for your second problem, "what if new_table_name already exists?", the only right and consistent way is to give error message. Exactly as it is done already.
If you want to redefine existing table, use ALTER TABLE statement.

I take it this isn't on the enhancement list for inclusion any time soon?

I think this would be a very valuable enhancement.

From the documentation:
"You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead"
http://dev.mysql.com/doc/refman/5.1/en/rename-table.html

For this reason, and for other reasons, I hope that MySQL will support ALTER TEMPORARY TABLE IF EXISTS. I never seen a situation where this is useful for non-temporary tables, but I think it is useful in some cases for temporary tables.

+1 vote for this feature. Are there any plans to implement this?

+1

Been roughly 5 and half years, and this still doesn't seem to have been introduced. 
Another +1. This is much needed.

11 Years. Still +1.

14 years, here is my +1

It's 2020. I am still waiting for this feature.