MySQL Delete

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

The MySQL Delete command removes 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.

Employee Table Rows 1

Deleting Data from MySQL Command Prompt

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

DELETE FROM sqltest.newemployees
WHERE EmpID = 1
Remove Table Rows from Command Prompt 2

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

MySQL Delete Single row

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

DELETE FROM 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 removing 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 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 remove a record. The below query eliminates rows whose FirstName is John and Sales was less than 1000.

DELETE FROM 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 eliminate the first five rows or last 10 rows as per your requirement. Here, we removed the first two records whose sales amount is high.

DELETE FROM 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 remove rows whose department id is null. You can try to remove rows whose id is not Null using IS NOT NULL.

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

Employee table, after removing 3 records

Remove Nulls from Table 12

Delete all rows

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

DELETE FROM newemployees;
MySQL Delete All Rows 13

Here, you can see an empty table

Empty Table Without any Records 14

Execute 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 below shown option.

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}>;