MySQL - Rename Tables

To rename a table into MySQL database through Node.js program, we need to execute RENAME TABLE statement using the query() function of the mysql2 library as −

sql = "RENAME TABLE table_name TO new_name";
con.query(sql);

To rename a table into MySQL database through Java program, we need to execute RENAME TABLE statement using the JDBC function executeUpdate() as −

String sql = "RENAME TABLE old_table_name TO new_table_name";
statement.executeUpdate(sql);

To rename a table into MySQL database through Python program, we need to execute RENAME TABLE statement using the execute() function of the MySQL Connector/Python as −

sql = "RENAME TABLE old_table_name TO new_table_name"
cursorObj.execute(sql);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "CREATE DATABASE tutorials"
  con.query(sql);

  //Selecting a Database
  sql = "USE tutorials"
  con.query(sql);

  //Creating DEMO table
  sql = "CREATE TABLE Demo(ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255));"
  con.query(sql);

  //Inserting records
  sql = "INSERT INTO Demo VALUES(1, 'Shikhar', 'Dhawan'),(2, 'Jonathan', 'Trott'),(3, 'Kumara', 'Sangakkara');"
  con.query(sql);

  //Fetching the DEMO table
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Following is the DEMO table**");
    console.log(result);
    console.log("--------------------------");
  });

  //Renaming the DEMO table as PLAYERS
  sql = "RENAME TABLE Demo to Players;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Renamed the DEMO table as Players**");
    console.log(result);
    console.log("--------------------------");
  });

  //Trying to Retrieve the DEMO table, Leads to an error.
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("Trying to retrieve DEMO table");
    console.log(result);
  });
});                   

Output

The output produced is as follows −

Connected!
--------------------------
**Following is the DEMO table**
[
  { ID: 1, First_Name: 'Shikhar', Last_Name: 'Dhawan' },
  { ID: 2, First_Name: 'Jonathan', Last_Name: 'Trott' },
  { ID: 3, First_Name: 'Kumara', Last_Name: 'Sangakkara' }
]
--------------------------
**Renamed the DEMO table as Players**
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
--------------------------
C:\Users\Lenovo\desktop\JavaScript\connectDB.js:52
    if (err) throw err
             ^

Error: Table 'tutorials.demo' doesn't exist
    at Packet.asError (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\connection.js:478:34)
    at PacketParser.onPacket (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\connection.js:97:12)
    at PacketParser.executeStart (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket. (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\connection.js:104:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10) {
  code: 'ER_NO_SUCH_TABLE',
  errno: 1146,
  sqlState: '42S02',
  sqlMessage: "Table 'tutorials.demo' doesn't exist",
  sql: 'SELECT * FROM Demo;',
  fatal: true
}        

import java.sql.*;
public class RenameTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Rename tables...!
            String sql = "RENAME TABLE tutorials_tbl TO new_table";
            statement.executeUpdate(sql);
            System.out.println("Table renamed successfully successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}                                

Output

The output obtained is as shown below −

Table renamed successfully successfully...!      

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
old_table_name = 'tutorials_tbl'
new_table_name = 'tutorials_table'
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"RENAME TABLE {old_table_name} TO {new_table_name}")
print(f"Table '{old_table_name}' is renamed to '{new_table_name}' successfully.")
cursorObj.close()
connection.close()                                           

Output

Following is the output of the above code −

Table 'tutorials_tbl' is renamed to 'tutorials_table' successfully.