Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Modifying existing data in SQL

  • Last Updated : 10 Sep, 2021

In this article, we are going to cover how we can modify the existing data in SQL. There are lots of situations where we need to alter and need to update existing data. Let’s discuss one by one. 

1. ALTER Command : 
ALTER is an SQL command used in Relational DBMS and is a Data Definition Language (DDL) statement. ALTER can be used to update the table’s structure in the database (like add, delete, drop indexes, columns, and constraints, modify the attributes of the tables in the database). 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

ALTER command is most commonly used to improve SQL SELECT queries by adding and removing indexes. 

SYNTAX : 
Adding a column to the existing table – 



ALTER TABLE tableName 
ADD columnName columnDefinition;

Example –  

ALTER TABLE Student 
ADD marks_obtained Number (3);

Before : Student Table 

nameclasscontactcity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

After : Student Table 

marks_obtained 

nameclasscontactcity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

SYNTAX : 
Removing column from existing table –  

ALTER TABLE tableName 
DROP COLUMN columnName;

Example –  

ALTER TABLE Student 
DROP COLUMN city;

Before : Student table  

nameclasscontactcity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

After: Student table 



nameclasscontact
ashu1090000
santosh1090001
pankaj1090002
deepak1090003

SYNTAX : 

Changing column name in the existing table –  

ALTER TABLE tableName 
RENAME COLUMN olderName TO newName;

Example –  

ALTER TABLE student 
RENAME COLUMN contactTO contact_no;

Before : Student table  

nameclasscontactcity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

After : Student table  

nameclasscontact_nocity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

2. UPDATE Command : 

UPDATE is an SQL command used in Relational DBMS and is a Data Manipulation Language (DML) statement. It is used to manipulate the data of any existing column. But can’t change the table’s definition. 

SYNTAX : 
Updating data in existing table –  

UPDATE table_name 
SET column1 = value1, 
column2 = value2, ... WHERE condition;

Example –  

UPDATE student 
SET contact = 91111. WHERE name =ashu;

Before : Student table  

nameclasscontactcity
ashu1090000Delhi
santosh1090001Delhi
pankaj1090002Delhi
deepak1090003Delhi

Without the WHERE clause, all records in the table will be updated. 

Difference Between ALTER and UPDATE Command in SQL :  

ALTER CommandUPDATE Command
It is a Data Definition Language (DDL).It is a Data Manipulation Language (DML).
It affects the structure of table.It affects the data of the table.
It is used to add, delete, drop indexes, columns, and constraints, modify the attributes of the tables in the databaseIt is used to UPDATE existing data in the database
It gives null value in tuple as default after initializing.It gives the specified values in the table as specified in the command.
Example – Table name, functions, table structure, etc.Changes data entry of specified column or row.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :