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.

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.

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

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 -

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 -

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 -

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 -

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 -

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 -

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 -

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 -

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 -