So, first, we need to create a temporary table using the following statement mysql> CREATE TEMPORARY TABLE cricket_runs(runs int NOT NULL) Rename Temporary tableĪ temporary table allows us to keep temporary data, which is visible and accessible in the current session only. Here we can see that electric_items changed into gadgets. Here we are going to change electric_items into gadgets mysql> RENAME TABLE electric_items TO gadgets Syntax: ALTER TABLE old_table_name RENAME TO new_table_name The following are the syntax of the ALTER TABLE statement The ALTER TABLE statement can also be used to rename the existing table in the current database. In above output we can see that the table name invoices into payments and table name orders into sales have successfully renamed. Mysql> RENAME TABLE invoices TO payments, orders TO sales Suppose if we want to change the table name invoices into payments and table name orders into sales, execute the following statement Suppose our database “sampledb_info” having the following tables mysql> SHOW TABLES Old_table_name2 TO new_table_name2, old_table_name3 TO new_table_name3 See the below statement RENAME TABLE old_table_name1 TO new_table_name1, RENAME TABLE statement in MySQL also allows us to change more than one table name within a single statement. Now we can confirm using below SQL statementĮRROR 1146 (42S02): Table 'sampledb_info.gadgets' doesn't existĥ rows in set (0.01 sec) 4. Mysql> RENAME TABLE gadgets TO electric_items
Next, execute the following syntax to change the table name mysql> USE sampledb_info Suppose we have a table named gadgets, and due to some reason, there is a need to change it into the table named electric_items. Let us understand how the RENAME TABLE statement works in MySQL through the various examples. We might be referred old existing table name in different other places like functions, stored procedures, views, triggers, foreign key constraints, etc.In terms of security, any existing privileges that we granted to the old table must be manually migrated to the new table.It would be better to follow the consistency in naming convention whether lower case or upper case all the characters in table name. Case sensitivity in MySQL depends on operating system.Table name must not exceed 64 characters.RENAME statement cannot change the name of a temporary table, but you can use the ALTER TABLE statement to rename a temporary table.If we use the RENAME TABLE statement, it is required to have ALTER and DROP TABLE privileges to the existing table.Let’s understand other basic rules that need to follow to rename database in MySQL. It is to ensure that the table is not locked as well as there are no active transactions before executing this statement. Otherwise, it will throw an error message. Here, we have to make sure that new_table_name must not exist, and old_table_name should be present in the database. RENAME TABLE old_table_name TO new_table_name
0 kommentar(er)
