Summary -

In this topic, we described about the DELETE Table with detailed example.

DELETE Table statement is used to DELETE the specified rows from existing table that those rows are not used in further. By using WHERE clause in the DELETE table statement we can delete specified rows. If we forgot WHERE clause in DELETE statement it will DELETE all rows from existing table.

Syntax -

DELETE FROM table_name
WHERE condition; 
  • table_name – Represents the name of the table.

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Deleting single row from existing table.

Requirement1 – Deleting one employee details from existing employee_details table whose designation is clerk. The query was as follows –

DELETE FROM employee_details WHERE designation = 'Clerk'; 

By executing above query we can delete single employee details of clerk in employee_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement2 – Deleting one employee details from existing employee_details table by using AND condition. The query was as follows –

DELETE FROM employee_details WHERE manager_id ='Employee3'
AND emp_id = 006; 

By using above query we can delete one employee of Analyst details from employee_details by using AND condition. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement3 – Deleting single employee details from existing employee_details table whose manager_id end with 'yee1' by using LIKE operator. The query was as follows –

DELETE from employee_details WHERE manager_id LIKE 'yee1'; 

By executing above query we can delete a single employee details from employee_details whose manager_id like 'yee1'. The output was as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Deleting multiple rows from existing table.

Requirement1 – Deleting multiple employee details from employee_details whose designation is Director. The query was as follows –

DELETE FROM employee_details WHERE designation = 'Director'; 

By executing above query we can delete multiple details of employees whose designation is Director. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement2 – Deleting multiple employee details from employee_details whose salary in between 15000 and 20000 by using BETWEEN operator. The query was as follows –

DELETE FROM employee_details WHERE salary BETWEEN 15000 AND 20000; 

By executing above query we can delete multiple details of employees whose salary in between 15000 and 20000. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement3 – Deleting multiple employee details from employee_details whose designation is clerk or salesman by using IN operator. The query was as follows –

DELETE FROM employee_details WHERE designation IN ('Clerk','Salesman'); 

By executing above query we can delete multiple details of employees whose designation either clerk or salesman by using In operator. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000

Requirement4 – Deleting multiple employee details from employee_details whose emp_id is 002 and emp_name is 'Employee4' by using OR operator. The query was as follows –

DELETE FROM employee_details WHERE emp_id = 002 
OR emp_name = 'Employee4';    

By executing above query we can delete multiple details of employees whose emp_id is 002 and emp_name is 'Employee4' by using OR operator. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement5 – Deleting multiple employee details from employee_details whose designation is Director and salary less than 50000 by using AND operator. The query was as follows –

DELETE FROM employee_details WHERE designation = 'Director'
 AND salary < 50000; 

By executing above query we can delete multiple details of employees from employee_details table whose designation is Director and salary less than 50000 by using AND operator. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000