20 - SQL Delete Records from Tables

DELETE Statement:

DELETE statement is used to delete one or more rows from a table.

Syntax:

DELETE FROM table_name

WHERE conditions;…

Example:

Delete example with 1 condition.

Suppose from the vendor management system, you have to delete a vendor row from the vendor_details table then use below query 

 

DELETE From VENDOR_DETAILS

WHERE VENDOR_NAME = 'COCO'

Here 1 or more rows for the COCO vendor would be deleted from the table. You can check if the rows have been deleted or not by using simple SELECT * FROM VENDOR_DETAILS.

Or run below query, once before the DELETE statement, and then after the DELETE statement.  There should be a difference in the  count.

 

SELECT COUNT(VENDOR_ID) FROM VENDOR_DETAILS

WHERE VENDOR_NAME = “COCO”;

 

Example 2: 

Delete records using more than 1 condition.

 

DELETE FROM VENDOR_DETAILS

WHERE VENDOR_NAME = “COCO” AND VENDOR_AREA = “CA”;

This would refine the records and only the rows with name “COCO” and area “CA” would be deleted.

Example3:

 What would be the result of below query?

 

DELETE FROM VENDOR_DETAILS;

The result would be all records would be deleted from VENDOR_DETAILS table, assuming all the referential keys relating to other tables have been taken care of.

Example 4:

Delete rows from 1 table depending on condition based on another table.

Consider below table of employees and Departments, Delete the employees of ACCOUNTS department.

 EMPLOYEE

+-----------+------------------------+---------+--------------------------+------------------+-------------+
| EMP_ID | EMMP_NAME     | AGEG | PHONE_NUM     | DEPT_ID     | SALARY  |
+-----------+------------------------+---------+--------------------------+------------------+-------------+
|      1 | john          |   35 | 100233023     |       1     |  15000  |
|      2 | Linda         |   30 | 100234565     |       2     |  15000  |
|      3 | Max           |   40 | 122222344     |       1     |  15000  |
|      4 | Will          |   40 |  12323424     |       3     |  15000  |
|      5 | Michal        |   45 |  12323434     |       1     |  15000  |
+-----------+------------------------+---------+--------------------------+------------------+-------------+

DEPARTMENTS

+-----------------+----------------------------+
| DEPT_ID     | DEPT_NAME      |
+-----------------+----------------------------+
|       1     | Accounts       |
|       2     | HR             |
|       3     | Production     |
+-----------------+----------------------------+
 

mysql> delete from employee

    -> where dept_id = (select dept_id from departments

    -> where dept_name = "accounts");

This would result in records with dept_id = 1 to be deleted from EMPLOYEE table.

+-----------+------------------------+---------+--------------------------+------------------+-------------+
| EMP_ID | EMMP_NAME     | AGEG | PHONE_NUM     | DEPT_ID     | SALARY |
+-----------+------------------------+---------+--------------------------+------------------+-------------+
|      2 | Linda         |   30 | 100234565     |       2     |  15000 |
|      4 | Will          |   40 |  12323424     |       3     |  15000 |
+-----------+------------------------+---------+--------------------------+------------------+-------------+

 

Like us on Facebook

Wideskills