Logo
×

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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)
How to delete a row in SQL

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.

Related Topics

SQL Insert multiple rows

This article will deal with a new insertion method in SQL which inserts multiple rows at a time. Multiple records can be inserted at a time into a specific table with...

4 minutes read.

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...

6 minutes read.

SQL Cast Operator

While writing SQL queries, we can see many values which are to be converted into another datatype for accessing them. In SQL this conversion is generally done by CAST function. CAST...

4 minutes read.

SQL Arithmetic Operators

This page contains all the information, learn about SQL Arithmetic Operators concept in the SQL table with the help of examples. The Arithmetic Operators is used to perform mathematical calculations on...

5 minutes read.

SQL TABLE

SQL TABLE Structured Query Language (SQL) is a relational database (RDBMS) where data is stored in the form of tables, that is, in rows and columns. These tables are known as...

3 minutes read.

SQL SELECT AVG

In this tutorial, we will learn about the aggregate function name avg() function concept in SQL with the help of examples. The AVG() function is one of the aggregate functions in...

3 minutes read.

Update Query in SQL

Update is an SQL command that is used to modify the data that in already present in database. Update is a command of DML. DML means Data Manipulation Language. Basically,...

3 minutes read.

SQL Truncate

This command deletes all records from table. Truncate is a DDL command. Syntax: TRUNCATE table table_name; Example: Truncate table teacher; ORDER BY The ORDER BY clause arranges the table or column in ascending...

5 minutes read.

SQL IN vs SQL EXISTS

SQL IN vs SQL EXISTS This article discusses in detail about the IN and the EXISTS operators in SQL. It is a common question between developers that what is the difference...

3 minutes read.

SQL Formatter

As a beginner, one does not focus much on Formatting the queries while writing their code. This leads to a great confusion while referring the code in future. For a...

5 minutes read.

SQL SELECT MIN

The SQL Min() function is an aggregate function in SQL. SQL Min() returns the minimum value of a given condition. The expression may be numerical, or it may be an expression. The syntax...

5 minutes read.

SQL INSERT INTO Values

This article will help you in getting a better understanding of a very important SQL INSERT INTO VALUES function. INSERT INTO statement is used to insert or add a new record...

4 minutes read.

SQL Commands

SQL commands are classified into four groups on the basis of their nature. DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) DQL (Data Query Language) NOTE: This...

1 minute read.

SQL WHERE Statement

SQL WHERE Statement Introduction WHERE clause is used to include a condition while fetching data from tables.When you have to specify a condition that has to be obeyed while data is...

9 minutes read.

SQL Injection

SQL injection is a technique, this may destroy the database. It is one type of hacking technique. SQL IN WEB PAGES: Injection occurs when we ask for input like an id or...

3 minutes read.

SQL Aliases

SQL Aliases: These are used to give a temporary name for a column or table. These are used to make tables or columns more readable. These are used to rename the table...

2 minutes read.

SQL CRUD Operation

In any computer language, CRUD operation is a foundation. CRUD operation rules are applied in databases as well. These operations are the basic operations used to perform with any database...

7 minutes read.

SQL Data Control Language

Data Control Language decides to whom should (which user) permit access privileges. GRANT and REVOKE are the commands of DCL. GRANT: It gives privileges to user. REVOKE: It takes back privileges from granted...

1 minute read.

SQL CASE

This page contains all the information about SQL CASE. The CASE is an If-Else type of logical query used in the statement. The CASE in Structured Query Language is similar...

5 minutes read.

SQL Alter Table

In Structured Query Language, if you want to add columns in an existing table, then modify the table, or delete columns from the table. All these operations are allowed only...

7 minutes read.