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;
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:
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:
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
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;
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.
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;
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;
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;
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;
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;
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);
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;
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;
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.
You can download the full source code of this example here: SQL Delete Query Example
Last updated on Nov. 07th, 2021
