MySQL DELETE Statement

MySQL DELETE statement is used to remove records from the MySQL table that is no longer required in the database. This query in MySQL deletes a full row from the table and produces the count of deleted rows. It also allows us to delete more than one record from the table within a single query, which is beneficial while removing large numbers of records from a table. By using the delete statement, we can also remove data based on conditions.

Once we delete the records using this query, we cannot recover it. Therefore before deleting any records from the table, it is recommended to create a backup of your database. The database backups allow us to restore the data whenever we need it in the future.

Syntax:

The following are the syntax that illustrates how to use the DELETE statement:

DELETE FROM table_name WHERE condition;

 

In the above statement, we have to first specify the table name from which we want to delete data. Second, we have to specify the condition to delete records in the WHERE clause, which is optional. If we omit the WHERE clause into the statement, this query will remove whole records from the database table.

If we want to delete records from multiple tables using a single DELETE query, we must add the JOIN clause with the DELETE statement.

If we want to delete all records from a table without knowing the count of deleted rows, we must use the TRUNCATE TABLE statement that gives better performance.

Let us understand how the DELETE statement works in MySQL through various examples.

MySQL DELETE Statement Examples

Here, we are going to use the “Employees” and “Payment” tables for the demonstration of the DELETE statement. Suppose the Employees and Payment tables contain the following data:

If we want to delete an employee whose emp_id is 107, we should use the DELETE statement with the WHERE clause. See the below query:

mysql> DELETE FROM Employees WHERE emp_id=107;  

After the execution of the query, it will return the output as below image. Once the record is deleted, verify the table using the SELECT statement:

If we want to delete all records from the table, there is no need to use the WHERE clause with the DELETE statement. See the below code and output:

In the above output, we can see that after removing all rows, the Employees table will be empty. It means no records available in the selected table.

MySQL DELETE and LIMIT Clause

MySQL Limit clause is used to restrict the count of rows returns from the result set, rather than fetching the whole records in the table. Sometimes we want to limit the number of rows to be deleted from the table; in that case, we will use the LIMIT clause as follows:

DELETE FROM table_name  

WHERE condition   

ORDER BY colm1, colm2, ...  

LIMIT row_count;

It is to note that the order of rows in a MySQL table is unspecified. Therefore, we should always use the ORDER BY clause while using the LIMIT clause.

For example, the following query first sorts the employees according to their names alphabetically and deletes the first three employees from the table:

mysql> DELETE FROM Employees ORDER BY name LIMIT 3;  

It will give the below output:

MySQL DELETE and JOIN Clause

The JOIN clause is used to add the two or more tables in MySQL. We will add the JOIN clause with the DELETE statement whenever we want to delete records from multiple tables within a single query. See the below query:

mysql> DELETE Employees, Payment FROM Employees   

INNER JOIN Payment      

ON Employees.emp_id = Payment.emp_id      

WHERE Employees.emp_id = 102;

Output:

After execution, we will see the output as below image:

To read more information about the DELETE statement with the JOIN clause, click here.