MySQL Delete

MySQL Delete Statement helps us to delete the unwanted rows or data from a table. You can use this MySQL delete command to delete both the temporary tables and the permanent tables.

The MySQL Delete command deletes a single row or multiple rows or all the records in a table. It is one of the powerful commands to remove unnecessary data from a database table.

MySQL Delete Syntax

The basic syntax of the MySQL Delete command is as shown below 

DELETE FROM `Schema_Name`.`Table_Name` 
[WHERE Conditions]; — Optional

Let me use the below-shown table to demonstrate this delete command.

MySQL Delete Command 1

Deleting Data from MySQL Command Prompt

In this example, we are going to use the command prompt or Terminal to delete a record from a table.

DELETE FROM sqltest.newemployees
WHERE EmpID = 1
MySQL Delete Command 2

NOTE: If the Where Clause not used, then the Delete command will remove all the records from a Table. So, always use Where clause to restrict the deleted records.

MySQL Delete Single row

In this example, we used the Where Clause to delete a record whose employee Id = 4.

DELETE FROM sqltest.newemployees
WHERE EmpID = 4;
MySQL Delete Command 3

Let us see the remaining data

MySQL Delete single row 4

MySQL Delete Multiple rows

Here, we are deleting the records whose department Id = 1. As you see, there are multiple rows linked with this department Id. Let us see how many of them removed?

DELETE FROM sqltest.newemployees
WHERE DeptID = 1;
MySQL Delete multiple rows 5

As you can see from the below screenshot, It has removed all the employees whose dept id = 1

MySQL Delete multiple rows 6

MySQL Delete with Multiple Conditions

Let me show you, how to use multiple conditions in where clause to delete a record. The below query delete rows whose FirstName is John and Sales was less than 1000.

DELETE FROM sqltest.newemployees
WHERE FirstName = 'John'  AND Sales < 1000;
MySQL Delete multiple condition 7

and the remaining data inside this table is

MySQL Delete multiple condition 8

MySQL Delete Limit 

By using the Order By Clause and the Limit Clause, you can delete the first five rows or last 10 rows as per your requirement. Here, we deleted the first two records whose sales amount is high.

DELETE FROM sqltest.newemployees
ORDER BY Sales DESC LIMIT 2;
MySQL Delete Limit 9

Remaining data in this new employee table

 MySQL Delete Order By 10

Delete rows with Nulls

This query deletes rows whose department id is null. You can try to delete rows whose id is not Null using IS NOT NULL.

DELETE FROM sqltest.newemployees
WHERE DeptID IS NULL;
MySQL Delete Nulls 11

Employee table, after deleting 3 records

MySQL Delete Nulls 12

Delete all rows

If you forgot to include where clause, Delete command removes all the records from a table.

DELETE FROM sqltest.newemployees;
MySQL Delete Nulls 13

Here, you can see an empty table

MySQL Delete all rows 14

Execute Delete Command from Workbench

If you have an option to access the MySQL Workbench, you can select the table on which you want to perform delete operation. Next, select the Send to SQL Editor and then select the Delete statement.

MySQL Delete Command 15

It generates the following MySQL code. You can alter the where clause as per your requirement.

DELETE FROM `sqltest`.`department4`
WHERE <{where_expression}>;