SQL MODIFY COLUMN

In this SQL article, we will discuss how to modify the column of the table in Structured Query Language.

What is MODIFY COLUMN Statement?

In many situations, you may require to modify the existing columns or fields of the existing table. So, SQL provides the MODIFY keyword with ALTER TABLE command to overcome this problem.

The MODIFY keyword modifies the size, datatype, and constraints of the existing field of the SQL table.

Syntax of MODIFY Column statement

This ALTER syntax allows us to modify the single field of the existing table.

If you want to modify more than one new field of the same SQL table in a single statement, then you have to use the following syntax:

ALTER TABLE Table_Name MODIFY Column_Name_1 New_Definition_of_Existing_Column_1,
MODIFY Column_Name_2 New_Definition_of_Existing_Column_2,
MODIFY Column_Name_3 New_Definition_of_Existing_Column_3,
.......,
MODIFY Column_Name_N New_Definition_of_Existing_Column_N;

Example of MODIFY Column statement in SQL

If you want to learn how to modify the column of the table, you have to create a new table.

The following statement creates the Teacher_Details table in SQL:

CREATE TABLE Employee_Grade
(
Employee_ID INT PRIMARY KEY,  
First_Name VARCHAR (100),  
Last_Name VARCHAR (100), 
First_City Varchar(120),
Second_City Varchar(120),
New_City Varchar(120),
Attendance_Remarks INT, 
Work_Remarks INT, 
Grade Varchar (80)
);

The below INSERT queries insert the records of Employees with Grades and Remarks in the Employee_Grade table:

INSERT INTO Employee_Grade (Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) VALUES (10, Ramesh, Sharma, Lucknow Aurangabad, Ghaziabad, 88, 95, A2);

INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) 
VALUES ( 02, Yadu, Sharma, Aurangabad, Ghaziabad, Noida, 95, 82, A1 );

INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) 
VALUES (07, Vijay, Ramna, Noida, Ghaziabad, Lucknow, 91, 95, A1);

INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade)  
VALUES (04, Bhanu, Rangopalr, Ghaziabad, Noida, Lucknow, 85, 82, NULL);

INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) 
VALUES (11, Harry, Roy, Noida, Kanpur, Ghaziabad, 95, 97, A1);

INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) 
VALUES (16, Akash, Ramna, Ghaziabad, Meerut, Aurangabad, 95, 90, B1);


INSERT INTO Employee_Grade 
(Employee_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Work_Remarks, Grade) 
VALUES (19, Ram, Ramna, Lucknow, Ghaziabad, Aurangabad, 89, 95, NULL);

The following SELECT statement displays the inserted records of the above Employee_Grade table:

Employee_ID	First_Name	Last_Name	First_City	Second_City	New_City	Attendance_Remarks	Work_Remarks	Grade
10	Ramesh	Sharma	Lucknow	Aurangabad	Ghaziabad	88	95	A2
02	Yadu	Sharma	Aurangabad	Ghaziabad	Ghaziabad	95	82	A1
07	Vijay	Ramna	Noida	Ghaziabad	Lucknow	91	95	A1
04	Bhanu	Rangopalr	Ghaziabad	Noida	Lucknow	85	82	NULL
11	Harry	Roy	Noida	Kanpur	Ghaziabad	95	97	A1
16	Akash	Ramna	Ghaziabad	Meerut	Aurangabad	95	90	B1
19	Ram	Ramna	Lucknow	Ghaziabad	Aurangabad	89	95	NULL

Query 1: The following ALTER query modifies the Grade column of the above table:

This query makes the Grade column as 'NOT NULL' in the table. Now, this column will not store NULL value.

Query 2: The following ALTER query modifies the New_City column of the above Employee_Grade table:

This query changes the size of the new city column of the table.

Query 3: The following ALTER query modifies the multiple columns of the above table:

ALTER TABLE Employee_Grade MODIFY First_Name Varchar(150), MODIFY Last_Name Varchar(150), MODIFY First_City Varchar(70);

This query changes the size of the first name, last name, and the first city of the Employee_grade table.

Next TopicMAKE_SET Function in SQL

← prev next →

Latest Courses

We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks

Contact info

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

[email protected].

Frontend

Data Analytics

Database

Online Compiler

Backend

Technical Interview

Java Interview

Web Interview

Database Interview

Company Interviews

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Misc

SQL MCQ

PL/SQL Tutorial

Interview

Quiz