MySQL Delete

MySQL Delete Statement helps us to remove unwanted rows or data from a table. You can use this delete command to remove both the temporary and 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 most powerful commands to remove unnecessary data from a database table.

MySQL Delete Syntax

The basic syntax of the 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 MySQL delete command.

Employee Table Rows 1

MySQL Delete Data from Command Prompt

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

DELETE FROM sqltest.newemployees
MySQL Delete Table Rows from Command Prompt 2

NOTE: If the Where Clause is not used, then this command will remove all the records from a Table. So, always use the 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

View rows 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

Check Table 6

MySQL Delete with Multiple Conditions

Let me show you how to use multiple conditions in the where clause to remove a record. The below query eliminates rows whose FirstName is John and whose Sales were 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

View Rows 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
MySQL Delete Limit 9

Remaining data in this new employee table

View Table 10

Delete rows with Nulls

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

DELETE FROM newemployees
IS Null 11

Employee table, after removing 3 records

Remove Nulls from Table 12

Delete all rows

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

DELETE FROM newemployees;
All Rows 13

Here, you can see an empty table

Empty Table Without any Records 14

Execute from Workbench

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

Workbench example 15

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

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