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:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:4.0.20 OS:Linux (Gentoo linux)
Assigned to: CPU Architecture:Any

[26 Jul 2004 16:38] Karel Vervaeke
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.
[25 Dec 2005 12:34] Valeriy Kravchuk
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.
[21 Apr 2008 15:27] Darren Arrowsmith
I take it this isn't on the enhancement list for inclusion any time soon?
[18 Nov 2009 19:22] Mark Boxall
I think this would be a very valuable enhancement.
[9 Dec 2009 14:08] Federico Razzoli
From the documentation:
"You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead"

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.
[19 Sep 2010 15:28] Zaar Hai
+1 vote for this feature. Are there any plans to implement this?
[17 Jan 2011 20:09] Ben Sandberg
[2 May 2011 8:29] Om Upadhyay
Been roughly 5 and half years, and this still doesn't seem to have been introduced. 
Another +1. This is much needed.
[15 Feb 2016 17:36] Monte Ohrt
11 Years. Still +1.
[10 Apr 2019 13:28] Marcus Puchalla
14 years, here is my +1
[10 Sep 2020 5:46] janardan chaudhary
It's 2020. I am still waiting for this feature.