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