MySQL DELETE Statement with Examples

How to Use DELETE Statement in MySQL

In this tutorial, you'll learn how to use the MySQL DELETE statement to delete records from a table in a database.

To delete one or more records from a table in MySQL database, you write the MySQL DELETE statement as follows:

WHERE conditions;

In the above statement, you need to specify the existing table from which you want to delete records and conditions in the WHERE clause.

MySQL DELETE Statement Examples

For example, let's say you have the employees table that consists of 4 columns, employee_idemployee_namedate_of_birth, and department, in our database. The following is our demo data:

#ID_300001Josefina JohnsonMarch 21st 1986IT
#ID_300002Charles HarmonFebruary 2nd 1988Finance and Accounting
#ID_300003Glen WoodAugust 27th 1990Sales and Marketing
#ID_300004Johanna CastilloAugust 24th 1992IT
#ID_300005Owen ClaytonSeptember 26th 1995Sales and Marketing
#ID_300007Francis LewisJuly 14th 1996Business Development
#ID_300008Holly RobertsonJuly 25th 1997Sales and Marketing
#ID_300009Michele LopezDecember 3rd 1998Business Development

Delete a Single Record

In this example, I show how to delete a single record in a single SQL query.

Let's say you want to delete a record, that employee_id is equal to #ID_300002, then you can write the statement as follows:

DELETE FROM employees
WHERE employee_id = "#ID_300002";

MySQL deletes any record, which employee_id is #ID_300002. Since there is only one record matches the condition in the WHERE clause, only one record is affected.

Delete Multiple Records

In this example, I show how to delete multiple records in a single statement.

More than records will be deleted from a table if those records meet the condition we specify in the WHERE clause.

Let's say you want to delete records based on the department column and take the IT department, for example.

The MySQL DELETE statement will look like this:

DELETE FROM employees
WHERE department = "IT";

Again, if you look at the above table, as you can see, two records meet the specified condition above, so MySQL will delete those two records when executing this statement.

In this tutorial, you've learned how to use the DELETE statement to delete one or more records from the existing table in a database. MySQL deletes only records that they meet the specified conditions.

See also:
MySQL LIKE Operator with Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function with Examples

Leave a Comment