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.
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
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;
Let us see the remaining data
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;
As you can see from the below screenshot, It has removed all the employees whose dept id = 1
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;
and the remaining data inside this table is
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;
Remaining data in this new employee table
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;
Employee table, after deleting 3 records
Delete all rows
If you forgot to include where clause, Delete command removes all the records from a table.
DELETE FROM sqltest.newemployees;
Here, you can see an empty table
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.
It generates the following MySQL code. You can alter the where clause as per your requirement.
DELETE FROM `sqltest`.`department4`
WHERE <{where_expression}>;
