Why to delete table data?

Table consists of data in the form of rows and columns. The data is inserted to the table in various ways. The reasons behind of deleting the table data are –

  • Data might insert wrongly.
  • Data might have loaded incorrectly.
  • The data storage requirement has fulfilled (like pending bills, pending transactions, pending payments etc,.) and many more.

DELETE FROM statement fulfils the requirement of deleting the data.

How to use DELETE FROM?

DELETE FROM statement used to delete all or some rows from an existing table. However, it will not delete the table from the database.

Syntax -

DELETE FROM table_name [WHERE <condition>];
  • table_name - Specifies the table name on which delete operation should take place.
  • condition - Specifies one or more conditions to get the specific records for deletion.

We can delete some specific rows by using WHERE clause. We can use multiple conditions based on requirement in WHERE clause. If WHERE clause is ignored in DELETE Statement, all the records get deleted 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 ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.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

Delete all rows from the table

We can delete all the rows of the table by using DELETE TABLE statement. The syntax is -

DELETE FROM table_name; 

In the below example, we are deleting all the rows from employee_details table. The query for the same is -

CREATE TABLE employee_details;

After successful execution of the above query, the data from employee_details table gets deleted. The table looks like below -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Delete specific rows from the table

We can delete some specific rows of the table by using DELETE TABLE statement. The syntax is –

DELETE FROM table_name WHERE condition;

In the below example, we are deleting rows from employee_details table that are having department 2000. The query for the same is -

CREATE TABLE employee_details WHERE dept_id = 2000; 

After successful execution of the above query, the employees who are belongs to department 2000 gets deleted from employee_details table. The table looks like below –

emp_id ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.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

DELETE Statement with Multiple conditions

We can delete some specific rows of the table by using DELETE TABLE statement. The condition can have multiple conditions in it. The syntax is –

DELETE FROM table_name WHERE condition1 [AND/OR] condition2…; 

In the below example, we are deleting rows from employee_details table that are having department 2000 or salary less than 18000. The query for the same is -

 CREATE TABLE employee_details WHERE dept_id = 2000 OR salary < 18000; 

After successful execution of the above query, the employees who are belongs to department 2000 or whose salary is < 18000 gets deleted from employee_details table. The table looks like below -

emp_id ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.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

DELETE Statement with LIKE Operator

We can delete some specific rows of the table by using DELETE TABLE statement using LIKE operator. The syntax is –

DELETE FROM table_name WHERE column_name LIKE expression; 

In the below example, we are deleting rows from employee_details table that are having designation starts with "Sales". The query for the same is -

CREATE TABLE employee_details WHERE designation LIKE "Sales"; 

After successful execution of the above query, the employees who are having designation starts with "Sales" word gets deleted from employee_details table. The table looks like below –

emp_id ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.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

DELETE Statement with BETWEEN Operator

We can delete some specific rows of the table by using DELETE TABLE statement using BETWEEN operator. The syntax is –

DELETE FROM table_name WHERE column_name BETWEEN first_value
 AND second_value; 

In the below example, we are deleting rows from employee_details table that are having salary in between 10000 and 20000 The query for the same is -

 CREATE TABLE employee_details WHERE salary BETWEEN 10000 AND 20000;

After successful execution of the above query, the employees who are having salary in between 10000 and 20000 gets deleted from employee_details table. The table looks like below -

emp_id ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.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