Tutorial Gateway

MySQL Delete

by

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 is used to delete single row or multiple rows or all the records in a table. This is one of the powerful command to remove the 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

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

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 records from a table.

DELETE FROM sqltest.newemployees
WHERE EmpID = 1

OUTPUT

MySQL Delete Command 2

MySQL Delete Single row

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

DELETE FROM sqltest.newemployees
WHERE EmpID = 4;

OUTPUT

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 we all know, there are multiple records associated with this department Id. Let us see, how many of them will be removed?

DELETE FROM sqltest.newemployees
WHERE DeptID = 1;

OUTPUT

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. Below query will delete rows whose FirstName is John and Sales was less than 1000.

DELETE FROM sqltest.newemployees
WHERE FirstName = 'John'  AND Sales < 1000;

OUTPUT

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

DELETE FROM sqltest.newemployees
ORDER BY Sales DESC LIMIT 2;

OUTPUT

MySQL Delete Limit 9

Remaining data in this new employee table

MySQL Delete Order By 10

Delete rows with Nulls

This query delete 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;

OUTPUT

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 will remove all the records from a table.

DELETE FROM sqltest.newemployees;

OUTPUT

MySQL Delete Nulls 13

Here, yo usan 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

This will generate the following code. You can alter the where clause as per your requirement.

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

Thank You for Visiting Our Blog