How to delete a row in SQL
Introduction
- To delete or remove the unused/unwanted rows from a table, DELETE command is used in SQL.
- DELETE command removes the entire record from a table.
- The DELETE statement can delete one or more than one data row from a table.
- To delete a specific row from a table, one needs to use the WHERE clause in SQL.
- If WHERE clause is not used in a DELETE query, then the entire table specified in the query will be deleted.
- The record deleted using DELETE query is permanently deleted from the table. Therefore, it is necessary that user maintains the backup of each table present in a database.
DELETE query with WHERE clause
User can specify the WHERE clause while writing a delete query. If WHERE clause is specified, then rows of a table specified in the delete query will be checked to see which particular row matches with the condition given in WHERE clause. Only the row that satisfies the where clause condition will be deleted.
Syntax:
DELETE from TableName WHERE condition;Example 1:
Consider a database with name “employee_db” with a table ‘employee’ created into it. In this topic, we will consider this table and database for all the subsequent examples.
mysql> USE employee_db;
Database changed
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 102 | Kavita | 35000 | Finance |
| 103 | Supriya | 55000 | Marketing |
| 104 | Sonam | 72000 | Finance |
| 105 | Sonal | 28000 | HR |
| 106 | Anjali | 49000 | Purchasing |
| 107 | Ankita | 61000 | R&D |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
8 rows in set (0.00 sec)
Here, we have selected the already created database with ‘USE employee_db’ command. Then we have used the SELECT command to display the employee table created into employee_db.
Now, we will write a query to delete a row from the table with employee name ‘Ankita’.
mysql> DELETE FROM employee WHERE Emp_Name = "Ankita" ;
Query OK, 1 row affected (0.17 sec)
DELETE command will be applied on ‘employee’ table. WHERE condition is written to check which row contains employee name equals to ‘Ankita’.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 102 | Kavita | 35000 | Finance |
| 103 | Supriya | 55000 | Marketing |
| 104 | Sonam | 72000 | Finance |
| 105 | Sonal | 28000 | HR |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
7 rows in set (0.00 sec)
When we again apply the SELECT command on ‘employee’ table just after applying the delete query written above, there is only one record which contains employee name equals to ‘Ankita’ ( Emp_ID = 107 ), whcih is removed from the table and the remaining rows are displayed as it is.
Example 2:
Write a query to delete a row from the table which contains employee name as ‘Sonal’ and employee department as ‘HR’.
We will first use the SELECT command to display all the records present in ‘employee’ table.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 102 | Kavita | 35000 | Finance |
| 103 | Supriya | 55000 | Marketing |
| 104 | Sonam | 72000 | Finance |
| 105 | Sonal | 28000 | HR |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
7 rows in set (0.00 sec)
Now, we will write a delete query to implement the given problem statement.
mysql> DELETE FROM employee WHERE Emp_Dept = "HR" AND Emp_Name = "Sonal" ;
Query OK, 1 row affected (0.09 sec)
DELETE command will be applied on ‘employee’ table. WHERE condition is written to check which row contains employee name equals to ‘Sonal’ and employee department equals to ‘HR’. ‘AND’ operator between both the condition restricts the system to check that both the conditions are met by a single record. Once both the conditions specified in WHERE clause are satisfied by any of the records then only those particular records will be deleted.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 102 | Kavita | 35000 | Finance |
| 103 | Supriya | 55000 | Marketing |
| 104 | Sonam | 72000 | Finance |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
6 rows in set (0.00 sec)
When we again apply the SELECT command on ‘employee’ table just after applying the delete query written above, there is only one record with Emp_ID = 105 which satisfies both the conditions of WHERE clause i.e., employee name is ‘Sonal’ and employee department is ‘HR’. So, only that particular record is deleted from the ‘employee’ table and the remaining rows are displayed as it is.
Example 3:
Write a query to delete a row from the table which contains employee department as ‘Finance’.
We will first use the SELECT command to display all the records present in ‘employee’ table.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 102 | Kavita | 35000 | Finance |
| 103 | Supriya | 55000 | Marketing |
| 104 | Sonam | 72000 | Finance |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
6 rows in set (0.00 sec)
Now, we will write a delete query to implement the given problem statement.
mysql> DELETE FROM employee WHERE Emp_Dept = "Finance" ;
Query OK, 2 rows affected (0.07 sec)
DELETE command will be applied on ‘employee’ table. WHERE condition is written to check which row contain employee department equals to ‘Finance’. Only the rows satisfying the given WHERE clause condition will be deleted.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 103 | Supriya | 55000 | Marketing |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
4 rows in set (0.00 sec)
When we again apply the SELECT command on ‘employee’ table just after applying the delete query written above, there are two records with employee id ‘102’ and ‘104’ who belong to the ‘finance’ department. Since both the records satisfy the given condition, both of them will be deleted from the ‘employee’ table and the remaining rows are displayed as it is.
Example 4: Write a query to delete the records from the table with salary less than or equal to 50000.
We will first use the SELECT command to display all the records present in ‘employee’ table.
mysql> SELECT *FROM employee;
+--------+----------+------------+------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+------------+
| 101 | Harshada | 40000 | Accounting |
| 103 | Supriya | 55000 | Marketing |
| 106 | Anjali | 49000 | Purchasing |
| 108 | Siddhi | 38000 | Purchasing |
+--------+----------+------------+------------+
4 rows in set (0.00 sec)
Now, we will write a delete query to implement the given problem statement.
mysql> DELETE FROM employee WHERE Emp_Salary <= 50000 ;
Query OK, 3 rows affected (0.05 sec)
DELETE command will be applied on ‘employee’ table. WHERE condition is written to check which row contain employee whose salary is less than or equals to ‘50000’. Only the rows satisfying the given WHERE clause condition will be deleted.
mysql> SELECT *FROM employee;
+--------+----------+------------+-----------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+-----------+
| 103 | Supriya | 55000 | Marketing |
+--------+----------+------------+-----------+
1 row in set (0.00 sec)
When we again apply the SELECT command on ‘employee’ table just after applying the delete query written above, there are three records with employee id ‘101’, ‘106’ and ‘108’ whose salary is less than or equal to ‘50000’. Since there are three records satisfying the given condition, all of them will deleted from the employee table and the remaining one row is displayed as it is.
DELETE query without WHERE clause
If a DELETE query is written without using a WHERE clause, then the entire table which is specified in the delete query will be deleted.
Syntax:
DELETE from TableName;Example: Write a query to delete all the records from the employee table.
mysql> SELECT *FROM employee;
+--------+----------+------------+-----------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept |
+--------+----------+------------+-----------+
| 103 | Supriya | 55000 | Marketing |
+--------+----------+------------+-----------+
1 row in set (0.00 sec)
In above, we have used the SELECT command to display all the records present in ‘employee’ table.
Now, we will write a delete query to implement the given problem statement.
mysql> DELETE FROM employee;
Query OK, 1 row affected (0.08 sec)
DELETE command will be applied on ‘employee’ table.
mysql> SELECT *FROM employee;
Empty set (0.00 sec)
mysql> DESC employee;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Emp_ID | int(11) | NO | PRI | NULL | |
| Emp_Name | varchar(20) | YES | | NULL | |
| Emp_Salary | int(11) | YES | | NULL | |
| Emp_Dept | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.16 sec)
In above example, when we again apply the SELECT command on ‘employee’ table just after applying the delete query written above, all the rows present in the ‘employee’ table are deleted.
After that we have used the DESC command to see the structure of the ‘employee’ table. We can see the structure of employee table even after applying delete query because delete query removes only the rows from the table and the structure will remain as it is.