MySQL Delete Statement

The MySQL Delete statement is used to delete or remove an existing record or records in a table. The Delete statement is one of the DML statements that MySQL supports.

MySQL Delete syntax

The Basic Syntax of a delete Clause is as follows:

DELETE FROM table_name WHERE condition;

We will use the MySQL Workbench to run these Queries. The method to run these queries in the MySQL Command-line client is the same.

MySQL Delete for Single Record

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

DELETE FROM DEPT_LOCATIONS WHERE LOCATION_ID=7;

MySQL Delete for 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;

MySQL Delete clause for 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 the 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.

MySQL Delete statement with LIMIT clause 

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;

MySQL Delete statement with Limit and Order by Clause

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

DELETE FROM EMPLOYEE_DEPT 
WHERE DEPT_ID=2 
ORDER BY RECORD_ID DESC
LIMIT 4;

MySQL Delete statement 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. The records from the rest of the tables are not deleted.

DELETE EMPLOYEES, EMPLOYEE_DEPT
FROM EMPLOYEES INNER JOIN EMPLOYEE_DEPT 
ON EMPLOYEES.EMP_ID = EMPLOYEE_DEPT.EMP_ID
WHERE EMPLOYEES.EMP_ID=23;

MySQL Delete statement from a single table using INNER JOINS

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;

MySQL Delete statement using LEFT JOINS

We can also use LEFT JOINs to connect two tables and to delete records. The same rule applies to 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;

MySQL Delete statement with 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);

MySQL Delete statement to delete all records

Delete all the records is also possible by omitting the Where clause. This, however, is usually not recommended. In some ways, it is similar to doing a "Truncate" table.

DELETE FROM EMPLOYEES2;