Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Rename Tables

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Rename Tables

      There can be a situation where both users and database administrators might want to change the name of a table in a relational database to make the table's name more suitable for a specific situation.
      MySQL provides two different ways to rename an MySQL table. We can use either the RENAME TABLE or ALTER TABLE statement. In this tutorial, we will understand them with suitable examples.

      MySQL RENAME TABLE Statement

      The MySQL RENAME TABLE statement is used to rename an existing table in a database with another name.

      Syntax

      Following is the basic syntax of the MySQL RENAME TABLE statement 
      RENAME TABLE table_name TO new_name;
      Where, table_name is the name of an existing table and new_name is the new name which you want to assign.

      Example

      Let us start by creating a table with name CUSTOMERS in MySQL database using CREATE statement as shown below 
      CREATE TABLE CUSTOMERS (
      	ID INT,
      	NAME VARCHAR(20),
      	AGE INT
      );
      Here, we are renaming the above-created CUSTOMERS table to BUYERS using the following query 
      RENAME TABLE CUSTOMERS to BUYERS;

      Output

      The table has been renamed without any errors.
      Query OK, 0 rows affected (0.01 sec)

      Verification

      Execute the following query to retrieve the description of the CUSTOMERS table 
      DESC CUSTOMERS;
      It display an error because, we have changed the CUSTOMERS table name to BUYERS and there is no CUSTOMERS table in our database.
      ERROR 1146 (42S02): Table 'tutorials.customers' doesn't exist

      Renaming Multiple Tables

      Using the MySQL RENAME TABLE statement, we can also rename multiple tables in a single query.

      Syntax

      Following is the syntax for renaming multiple tables using MySQL RENAME TABLE statement 
      RENAME TABLE old_table1 TO new_table1,
         old_table2 TO new_table2,
         old_table3 TO new_table3;

      Example

      In the following example, we are creating three different tables named Cust1, Cust2, and Cust3 
      CREATE TABLE Cust1(ID INT);
      CREATE TABLE Cust2(ID INT);
      CREATE TABLE Cust3(ID INT);
      Here, we are verifying whether the above tables are created or not using the following query 
      SHOW TABLES;
      As we can see in the output below, the above tables have been successfully created.
      Tables_in_tutorials
      cust1
      cust2
      cust3
      Now, let us rename all the above-created tables using the following query 
      RENAME TABLE Cust1 TO Buyer1, Cust2 TO Buyer2, Cust3 TO Buyer3;

      Output

      All three tables has been renamed without any errors.
      Query OK, 0 rows affected (0.03 sec)

      Verification

      Let us verify the list of the tables again to find whether the table names have been changed or not 
      SHOW TABLES;
      As we can see the output below, all three tables have been successfully renamed.
      Tables_in_tutorials
      buyer1
      buyer2
      buyer3

      Renaming a Table using ALTER TABLE statement

      In MySQL, we can also use the RENAME with ALTER TABLE statement to modify the name of an existing table.

      Syntax

      Following is the syntax to rename a table with ALTER TABLE statement 
      ALTER TABLE existing_table_name RENAME TO new_table_name

      Example

      In the following query, we are creating a table named PLAYERS.
      CREATE TABLE PLAYERS (
      	ID INT,
      	NAME VARCHAR(20),
      	AGE INT
      );
      Now, let us rename the above-created table with a new name TEAMS using the following query 
      ALTER TABLE PLAYERS RENAME TO TEAMS;

      Output

      The table has been renamed without any errors.
      Query OK, 0 rows affected (0.02 sec)

      Verification

      Execute the following query to retrieve the description of the PLAYERS table 
      DESC PLAYERS;
      It will display an error because, we have renamed the PLAYERS table to TEAMS and there is no PLAYERS table in our database.
      ERROR 1146 (42S02): Table 'tutorials.players' doesn't exist

      Renaming Table Using a Client Program

      In addition to renaming a table in MySQL Database using MySQL query, we can also perform the RENAME TABLE operation on a table using a client program.

      Syntax

      Following are the syntaxes to rename table in MySQL database in various programming languages
      PHPNodeJSJavaPython
      To rename a table into MySQL database through PHP program, we need to execute RENAME TABLE statement using the mysqli function query() as 
      $sql = "RENAME TABLE old_table_name TO new_table_name";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      $sql = "RENAME TABLE tutorials_table TO tutorials_tbl ";
      if ($mysqli->query($sql)) {
          printf("table renamed successfully.");
      }
      if ($mysqli->errno) {
          printf("table could not rename: %s", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      table renamed successfully.