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.

Alter in SQL

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.

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, '[email protected]', '9999999999'),
(2, 'Rahul', 22, '[email protected]', '8888888888'),
(3, 'Priya', 21, '[email protected]', '7777777777'),
(4, 'Sonia', 23, '[email protected]', '6666666666'),
(5, 'Kiran', 19, '[email protected]', '5555555555');

Output:

Example of ALTER (RENAME) in SQL

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:

To Add a New Column with ALTER 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:

Conclusion

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.