Summary -

In this topic, we described about the below sections -

Why DELETE Statement?

Let us assume we have a transactions table. The table contains the data from last three years. As per the requirement, the table should only contain last two years data and the remaining data should get deleted. To handle these kinds of requirements, we can use DELETE statement.

What is DELETE Statement?

DELETE statement is used to delete single, multiple rows or values from the existing table.

Syntax -

DELETE FROM table_name
 [WHERE condition]; 
  • table_name – Specifies the name of the table.
  • condition – Specifies the condition used to pick up the rows for deletion. In this condition, we can use comparison and logical operators.

DELETE statement with WHERE condition used to delete the rows that satisfies the condition with WHERE clause and remaining row untouched.

DELETE statement without WHERE condition used to delete all the rows from the table.


Note :- Be cautious while using DELETE statement. If we forgot WHERE clause in DELETE statement, it ends up deleting all rows from 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 all rows from table.

Requirement – Delete all rows from existing employee_details table. The query was as follows –

DELETE FROM employee_details;    

By executing above query, we can delete all rows from existing employee_details table.

Before confirming, verify the table by triggering SELECT query on the table. The query was as follows -

SELECT * FROM employee_details;  

By executing above query, we can get result as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Scenario – Delete single row using WHERE clause.

Requirement1 – Delete employee details who have employee id as 001. The query was as follows –

DELETE from employee_details WHERE emp_id = 001; 

By executing above query, we can delete employee details whose emp_id is 001. The table after deletion is as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
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

Requirement2 – Delete employee whose designation starts with "Cle". The query was as follows –

DELETE from employee_details WHERE designation LIKE "Cle%"; 

By executing above query, we can delete employee whose designation starts with "Cle%". The table after deletion is 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

Requirement3 – Delete employee who is "Manager" and having manager "Employee1". The query was as follows –

DELETE from employee_details WHERE designation= "Manager" 
 AND manager_id = 'Employee1'; 

By executing above query, we can delete an employee whose designation is "Manager" and having manager "Employee1". The table after deletion is 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 – Delete multiple rows using WHERE clause.

Requirement1 – Delete employee details who are under 1000. The query was as follows –

DELETE from employee_details WHERE dept_id = 1000; 

By executing above query, we can delete employees whose dept_id is 1000. The table after deletion is as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
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

Requirement2 – Delete employees who are having salary between 15000 and 25000. The query was as follows –

DELETE FROM employee_details 
WHERE salary BETWEEN 15000 AND 25000; 

By executing above query, we can delete employee details whose salary in between 15000.00 and 25000.00. The table after deletion is 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
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement3 – Delete employees whose degination starts with "D" and having 3rd and 4th letters are "re". The query was as follows –

DELETE FROM employee_details WHERE designation LIKE 'D_re%'; 

By executing above query, we can delete employee details whose designation match with 'D_re%'. The table after deletion is 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

Requirement4 – Delete employee details whose designation is "Manager" and salary is greater than 2000. The query was as follows –

DELETE FROM employee_details WHERE designation = "Manager" 
 AND salary > 2000;  

By executing above query, we can delete employees whose designation is manager and salary greater than 2000. The table after deletion is 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
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 list of rows from existing employee_details table by using OR OPERATOR. The query was as follows –

DELETE from employee_details WHERE manager_id = 'Employee3' OR
'Employee4'; 

By executing above query, we can delete multiple rows of employees whose manager is Employee3' OR 'Employee4'. The table after deletion is 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