Modifying existing data in SQL

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.