SQL | ALTER (RENAME)

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.

Note: Syntax may vary in different databases. 

Syntax(Oracle,MySQL,MariaDB):

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:

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.

Syntax:

ALTER TABLE Student RENAME COLUMN NAME TO FIRST_NAME;

Query:

ALTER TABLE Student RENAME name TO FIRST_NAME;

Output:

Change the name of the table Student to Student_Details.

Query:

ALTER TABLE Student RENAME TO Student_Details;

Output:

Student_Details

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:

This article is contributed by Shubham Chaudhary. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. 
Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save