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. |