Open In App

SQL Query to Add a New Column After an Existing Column in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command.

ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.

Step 1: Create a database employee.

Query:

CREATE DATABASE employee;

Step 2: Create a table

Now create a table employee_info.

Query:

CREATE TABLE employee_info
( Employee_id INT,
First_Name VARCHAR(25),
Last_Name VARCHAR(25),
Salary INT,
City VARCHAR(20)); 

INSERT INTO employee_info
VALUES (1,'Monika','Singh',800000,'Nashik'),
(2,'Rahul','Kumar',450000,'Mumbai'),
(3,'Sushant','Kumar',500000,'Pune'),
(4,'Ajay','Mehta',600000,'Mumbai');

 

Step 3: To view a database schema we use the following query.

Query:

EXEC sp_help 'dbo.employee_info';

Output:

 

Now, let’s add a new column Gender in the table. Then we use ALTER table command. 

Step 4: Alter the table.

ALTER TABLE employee_info ADD Gender CHAR(1) CHECK (Gender IN ('M','F'));

 

Output:

 

Now, the new column gets added after City i.e. at the last successfully.

Take another case using a query, If we want the Gender column after Last_Name, then we can write the query as shown below.

Query:

SELECT Employee_Id,First_Name,Last_Name,Gender,Salary,City FROM employee_info;

Output:

 

To Rename a Column in a MySQL Table

The old column can be replaced with a new name in MySQL using the ALTER TABLE CHANGE COLUMN statement. The syntax to use for this is as follows:

Syntax:

ALTER TABLE table_name

CHANGE GCOLUMN old_column_new_column_name column_definition[FIRST|AFTER exisiting_column];

In the above condition:

  1. The table name needs to be specified first.
  2. The new column name and its definition must be specified after the CHANGE COLUMN clause, along with the old column name. Even though the column definition won’t change, we still need to specify it.
  3. Finally, we must specify whether the keyword is FIRST or AFTER. The information indicating when to change the column name at a particular position is optional.

Let’s take an example that we have one table and we will add one rename one column in the table.

CREATE TABLE:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'Sales', 50000),
       (2, 'Jane Smith', 'Marketing', 60000),
       (3, 'Bob Johnson', 'HR', 45000),
       (4, 'Sara Lee', 'IT', 80000);

Output:

 

Query:

ALTER TABLE employees
RENAME COLUMN salary TO annual_salary;

Output:

 

Before removing a column from the table, keep in mind the following important details:

  1. The schema of one table may be dependent on the columns of another table in relational databases where MySQL is used. Therefore, removing a column from one table will have an impact on all related tables as well. As you remove the column, keep the following in mind:
  2. Triggers, stored procedures, and views are just a few examples of objects that will be impacted when columns are removed from a table. Let’s say we remove a column that the trigger is referencing. The changed code for the dropped column depends on other application code, which requires time and effort.

Last Updated : 08 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads