ALTER (RENAME) in SQL

Sometimes we may want to rename our table to give it a more relevant name. For this purpose, we can use ALTER TABLE to rename the name of the table. SQL ALTER TABLE is a command used to modify the structure of an existing table in a database. Here we will discuss Alter Command in SQL in detail.

Note:

Syntax may vary in different 

Here we are discussing the syntax of Alter Command in different Databases like MYSQL, MariaDB, Oracle, etc. Let’s proceed with Syntax first.

Syntax

ALTER TABLE table_name

RENAME TO new_table_name;

Columns can also be given a new name with the use of ALTER TABLE.

Syntax(MySQL, Oracle)

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

Syntax(MariaDB)

ALTER TABLE table_name

CHANGE COLUMN old_name TO new_name;

Query

CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(50),
  phone VARCHAR(20)
);

Let’s insert some data and then perform ALTER operation to understand better bout alter command.

INSERT the data in Student Table

INSERT INTO Student (id, name, age, email, phone) 
VALUES 
(1, 'Amit', 20, 'amit@gmail.com', '9999999999'),
(2, 'Rahul', 22, 'rahul@yahoo.com', '8888888888'),
(3, 'Priya', 21, 'priya@hotmail.com', '7777777777'),
(4, 'Sonia', 23, 'sonia@gmail.com', '6666666666'),
(5, 'Kiran', 19, 'kiran@yahoo.com', '5555555555');

Output

Example 1:

Change the name of column name to FIRST_NAME in table Student. To change the column name of the existing table you have to use Column keyword before writing the existing column name to change

Syntax

ALTER TABLE Student RENAME COLUMN Column_NAME TO FIRST_NAME;

Query

ALTER TABLE Student RENAME Column name TO FIRST_NAME;

Output

Example 2:

Change the name of the table Student to Student_Details.

Query

ALTER TABLE Student RENAME TO Student_Details;

Output

Student_Details Table

To add a new column to the existing table, we first need to select the table with ALTER TABLE command table_name, and then we will write the name of the new column and its datatype with ADD column_name datatype. Let’s have a look below to understand better.

Syntax

ALTER TABLE table_name

ADD column_name datatype;

Query

ALTER TABLE Student ADD marks INT;

Output

In conclusion, you can use ALTER Command whenever you want to change the data in an existing table like datatype from int to float and CHAR to VARCHAR, rename the table column name and add a new column, etc. In this article, we discussed all the Syntax of ALTER commands in the different databases with examples.