Use of DELETE Command in DBMS

Delete command is a data manipulation command which is used to remove records from a table. All records may be removed in one go, or a set of records may be deleted based on a condition.

Remove specific rows from table

To remove a specific row in the table, we need to mention where condition. Based on the condition, that particular row is deleted from the table.

The syntax is as follows −

delete from tablename where condition

For example,

Delete from employee where name=’sneha’;

Example

Given below is an example to remove specific rows from table −

create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
select * from employee;

Output

You will get the following output −

ram|projectmanager|40|50000
priya|assistant director|45|45000
hari|developer|46|30000

Now, use the program given below −

Example

delete from employee where ename='priya';
select * from employee;

Output

You will get the following output −

ram|projectmanager|40|50000
hari|developer|46|30000

Removing data based on condition

For example,

delete from employee where age>45;

Example

Given below is an example to delete the rows of employees whose age is greater than 45, rest will be stay in database −

create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
select * from employee;

Output

You will get the following output −

ram|projectmanager|40|50000
priya|assistant director|45|45000
hari|developer|46|30000

Now, use the program given below −

delete from employee where age>45;
select * from employee;

Output

You will get the following output −

ram|projectmanager|40|50000
priya|assistant director|45|45000

Remove data based on the result of sub query

Delete may also be done based on the result of a subquery.

For example,

delete from employee where age in (select age from employee where age>45)

Remove all rows from a table

If we want to delete entire table we can use the following −

delete from employee;
(or)
delete * from employee;

To remove all rows from a table we can also use the TRUNCATE command.

The syntax is as follows −

TRUNCATE table tablename;

For example,

TRUNCATE table employee

TRUNCATE removes all rows from a table without backup.

Example

Given below is an example to remove all rows from a table −

create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);

delete from employee;
select * from employee;

Output

You will get the following output −

no rows displayed