SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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.



ADVERTISEMENT
ADVERTISEMENT