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).
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
name |
class |
contact |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
After : Student Table
marks_obtained
name |
class |
contact |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
SYNTAX :
Removing column from existing table –
ALTER TABLE tableName
DROP COLUMN columnName;
Example –
ALTER TABLE Student
DROP COLUMN city;
Before : Student table
name |
class |
contact |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
After: Student table
name |
class |
contact |
ashu |
10 |
90000 |
santosh |
10 |
90001 |
pankaj |
10 |
90002 |
deepak |
10 |
90003 |
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
name |
class |
contact |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
After : Student table
name |
class |
contact_no |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
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
name |
class |
contact |
city |
ashu |
10 |
90000 |
Delhi |
santosh |
10 |
90001 |
Delhi |
pankaj |
10 |
90002 |
Delhi |
deepak |
10 |
90003 |
Delhi |
Without the WHERE clause, all records in the table will be updated.
Difference Between ALTER and UPDATE Command in SQL :
ALTER Command |
UPDATE 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 database |
It 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. |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...