sql

SQL Delete Query Example

1. Introduction

In this article, we will look at one of the Data Manipulation Language (DML) constructs or statements called SQL Delete query using the MySQL RDBMS.

2. What is Data Manipulation Language?

In SQL, Data Manipulation language consists of SQL data-change statements that modify data but not the underlying schema or database objects. The commands included in the data manipulation language are:

  • Insert – add new records/tuples in table or tables.
  • Update – alter records/tuples already present.
  • Delete – remove records/tuples.

Unlike Data Definition statements, DML statements are not auto-commit transactions by default. Hence, we can undo the changes until we specifically “commit” the transaction. These commands correspond to the “CRUD” (Create-retrieve-update-delete) statements that most applications have.

2.1 What is a Transaction?

A transaction simply put is a unit of work, which is performed in the order in a database. Transactions are used to propagate or reverse one or more changes done to a database. Transactions are used for DML statements and are extremely important to ensure data integrity. More on transactions in general here.

3. Delete Syntax

The Basic Delete Syntax is as follows:

DELETE FROM table_name WHERE condition;

  • Table_name       = the name of the relation/table whose records need to be changed.
  • Condition      = To restrict the records updated.
  • In the Syntax above, the condition is particularly important because if the condition is not present i.e. “no where” clause is mentioned, then all the records of the table will be deleted.

    4. SQL Delete query in MySQL

    According to MySQL, the delete statement syntax for a singe table is as follows:

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
        [PARTITION (partition_name [, partition_name] ...)]
        [WHERE where_condition]
        [ORDER BY ...]
        [LIMIT row_count]
    

    The Syntax for Multi table is as follows

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        tbl_name[.*] [, tbl_name[.*]] ...
        FROM table_references
        [WHERE where_condition]
    
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        FROM tbl_name[.*] [, tbl_name[.*]] ...
        USING table_references
        [WHERE where_condition]
    

    The “LOW_PRIORITY” ,”QUICK” and “IGNORE” are the modifiers that are available with the Delete statement in MySQL.

    LOW_PRIORITY = This modifier makes the delete statement delay execution until no other connection is reading data from the table. This is used by table-level locking storage engines such as MyISAM, MERGE and MEMORY.

    QUICK = This modifier does not merge index leaves during delete. This helps speed up the process of deleting records and is specifically used for ISAM tables.

    IGNORE = This modifier enables the delete statement to ignore ignorable errors during delete and continue with the rest. Errors are returned as warnings.

    4.1 Setup for examples:

    For going through the examples related to Delete, we will consider the database called “TestDB” which has the following data and tables:

    SQL Delete - all tables
    Table information

    Along with these there is the employees2 table which is the exact replica of the ‘employees’ table. Also, the ratings table is a lookup table with a rating and a percentage hike in salary. For running the queries, we will use the MySQL Workbench, the documentation for which is available here.

    5. SQL DELETE Examples

    Here we will see the various delete statements and syntaxes.

    5.1 Delete Single Record

    To delete a single record, we would simply use a where clause which retrieves the exact record to delete. Hence, the query to delete a location with the id=7 is as follows:

     DELETE FROM DEPT_LOCATIONS WHERE LOCATION_ID=7;

    The output of the above query is as follows:

    SQL Delete - Delete a single row
    Delete a single row

    5.2 Delete Multiple records

    We need to delete all the employees whose email is null from the employees2 table. On checking we see that there are multiple such records. To delete all the records in one go, we use the query

    DELETE FROM EMPLOYEES2 WHERE EMAIL IS NULL;

    Output is

    SQL Delete - Delete multiple records
    Delete multiple records

    5.3 Delete from Tables with columns used as Foreign keys

    If a column from a table is used as a foreign key in some other table, then records from that table cannot be deleted unless the “referencing” table records are deleted. An example of the error thrown is as follows

     DELETE FROM EMPLOYEES WHERE EMP_ID = 23;
    SQL Delete - Foreign key error on delete
    Foreign key error on delete

    The EMP_ID column is used as a foreign key in employee_dept table. The other workaround is to mark the foreign key as “On DELETE CASCADE.” In such a case, when we delete the record from the “parent” i.e. in the above example employee table, the records from the employee_Dept table will automatically be deleted and the delete will be allowed.

    SQL Delete - Delete Foreign key on Delete Cascade
    Delete Foreign key on Delete Cascade

    5.4 Delete with LIMIT

    We can limit the number of records retrieved and hence deleted using the LIMIT clause added to a delete.

    DELETE FROM EMPLOYEE_DEPT 
    WHERE DEPT_ID=2 
    LIMIT 3;
    
    SQL Delete - Delete with Limit clause
    Delete with Limit clause

    We can also do an Order by and get the records in a sorted order and then apply the LIMIT clause

    DELETE FROM EMPLOYEE_DEPT 
    WHERE DEPT_ID=2 
    ORDER BY RECORD_ID DESC
    LIMIT 4;
    
    SQL Delete - Delete with LIMIT and ORDER BY
    Delete with LIMIT and ORDER BY

    5.5 Delete records from multiple tables using INNER JOINS

    We can use JOINs to connect multiple tables and delete records from them. The tables mentioned in the DELETE Clause are the ones from which records are deleted and The records from the rest of the tables are not deleted

    The Query to delete records from two tables using an inner join is as follows

    DELETE EMPLOYEES, EMPLOYEE_DEPT
    FROM EMPLOYEES INNER JOIN EMPLOYEE_DEPT 
    ON EMPLOYEES.EMP_ID = EMPLOYEE_DEPT.EMP_ID
    WHERE EMPLOYEES.EMP_ID=23;
    
    Delete from both tables with Inner Join
    Delete from both tables with Inner Join

    The Query to delete records from a single table using an inner join is as follows:

    DELETE EMPLOYEE_DEPT
    FROM EMPLOYEES INNER JOIN EMPLOYEE_DEPT 
    ON EMPLOYEES.EMP_ID = EMPLOYEE_DEPT.EMP_ID
    WHERE EMPLOYEES.EMP_ID=3 AND EMPLOYEE_DEPT.TO_DATE IS NOT NULL;
    
    Records for INNER JOIN
    Records for INNER JOIN
    Delete from single table with Inner join
    Delete from single table with Inner join

    5.6 Delete using LEFT JOINS

    We can also use LEFT JOINs to connect two tables and to delete records. The same rule applies as Inner Joins. Records are deleted only from the tables which are mentioned in the Delete Clause

    DELETE EMPLOYEES 
    FROM EMPLOYEES LEFT JOIN RATINGS
    ON EMPLOYEES.RATING = RATINGS.RATING_ID
    WHERE RATINGS.RATING_ID=1;
    
    Delete from table with LEFT Join
    Delete from table with LEFT Join

    5.7 Delete using Subqueries

    We cannot use the same table we are deleting records from, in a subquery. As an example

    DELETE FROM EMPLOYEES2 WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEES2 WHERE EMAIL IS NULL);
    Error for same table in Subquery
    Error for same table in Subquery

    5.8 Delete and Auto-Increment

    Auto-increment indexes are not re-used in Inno-DB Engine systems. So, if a particular record is deleted, then that auto-increment value is not used again unless specifically set. To understand this , we deleted the Max location_id from the DEPT_LOCATIONS table. Then, we will insert a new record using the “Default” value i.e. auto-incremented value of the location_id.

    SELECT MAX(LOCATION_ID) FROM DEPT_LOCATIONS;
    
    #DELETE QUERY TO DELETE MAX LOCATION ID
    DELETE FROM DEPT_LOCATIONS WHERE LOCATION_ID=9;
    
    #INSERT A NEW RECORD WITH DEFAULT VALUE FOR LOCATION_ID WHICH IS AUTO-INCREMENTED.
    INSERT INTO DEPT_LOCATIONS VALUES(DEFAULT,9,'MINAS TIRITH');
    
    SELECT * FROM DEPT_LOCATIONS ORDER BY LOCATION_ID DESC;
    
    Delete and Auto-increment
    Delete and Auto-increment

    To reset the Auto-increment column value or to set an Auto-incremented column to a particular value, we can use the alter table syntax. For example

    ALTER TABLE DEPT_LOCATIONS AUTO_INCREMENT = 9;

    More about how and why of Auto-increment is available here.

    5.9 Delete all records

    Delete all the records is also possible by omitting the Where clause. This however is usually not recommended and in some ways it is similar to doing a “Truncate” table. However, they are not the same concept at all.

    DELETE FROM EMPLOYEES2;
    Delete all records
    Delete all records

    6. Summary

    In the article, we saw syntaxes related to the SQL Delete statement in MySQL. Delete is one of the important CRUD operations and these operations form an integral part of any application. The complete documentation of the Delete Clause is available here.

    7. Download the Source Code

    This was an example of the SQL Delete Query using MySQL RDBMS. Besides the delete Queries, there are a few Queries to know what foreign key constraints are present on your tables. Also, the queries for how to alter foreign key constraints for tables are also present.   

    Download
    You can download the full source code of this example here: SQL Delete Query Example

    Last updated on Nov. 07th, 2021

    Reshma Sathe

    I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
    Subscribe
    Notify of
    guest

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    0 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments
    Back to top button