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:
DELETE FROM table
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_id, employee_name, date_of_birth, and department, in our database. The following is our demo data:
|#ID_300001||Josefina Johnson||March 21st 1986||IT|
|#ID_300002||Charles Harmon||February 2nd 1988||Finance and Accounting|
|#ID_300003||Glen Wood||August 27th 1990||Sales and Marketing|
|#ID_300004||Johanna Castillo||August 24th 1992||IT|
|#ID_300005||Owen Clayton||September 26th 1995||Sales and Marketing|
|#ID_300007||Francis Lewis||July 14th 1996||Business Development|
|#ID_300008||Holly Robertson||July 25th 1997||Sales and Marketing|
|#ID_300009||Michele Lopez||December 3rd 1998||Business 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.