Summary -

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

Sometimes, there are requirements to delete the specific row that might be updated wrongly or the row is no longer needed. For example, pending transaction details.

DELETE statement used to delete the row from the table and frees up the memory that is utilized by the row till the deletion. We should all make a note that the deleted data, won't be recovered until or unless it is specified as recoverable.

Executing DELETE statement with WHERE clause deletes the matched rows from the table. So be cautious while using DELETE statement, it can end up by deleting the whole table data if you are not used WHERE clause.

Syntax -

DELETE FROM table_name WHERE condition; 
  • table_name – Specifies 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 a single row from existing table.

Requirement1 - Delete employee details from employee_details whose salary is 13000. The query was as follows –

DELETE FROM employee_details WHERE salary = 13000; 

By executing above query, we can delete an employee details whose salary is 13000 from employee_details. The remaining table 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
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000

Requirement2 - Deleting employee details from employee_details whose designation is "Director" AND employee id is 001. The query was as follows –

DELETE FROM employee_details WHERE designation = "Director"
AND emp_id = 001; 

By executing above query, we can delete an employee details whose designation is "Director" and emp_id is 001. The remaining table was 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

Requirement3 - Deleting employee details from employee_details whose date_of_hire ends with '09'. The query was as follows –

DELETE FROM employee_details WHERE date_of_hire LIKE '09'; 

By executing above query, we can get one employee details from employee_details whose date_of_hire end with '09'. The remaining table 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
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000