How to Use the DELETE Statement in MySQL
In this tutorial, you will learn how to use the MySQL DELETE statement to delete rows from a table in a database.
These are four basic SQL statements: SELECT (retrieve rows), INSERT (adding rows), UPDATE (update the existing rows), and DELETE (delete rows).
This tutorial explains how to delete rows using the DELETE statement.
To delete one or more rows from a table in a MySQL database, write a MySQL DELETE statement like this:
DELETE FROM table
WHERE conditions;
In the above statement, you need to specify the existing table from which you want to delete the records and conditions in the WHERE clause.
MySQL DELETE Statement Examples
For example, suppose you have a table named employees in your database that consists of 4 columns: employee_id, employee_name, date_of_birth, and department.
| employee_id | employee_name | date_of_birth | department |
|---|---|---|---|
| #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 row
This example shows how to delete a single row.
If you want to delete a row where employee_id is equal to #ID_300002, you can write a statement like this:
DELETE FROM employees
WHERE employee_id = "#ID_300002";
MySQL deletes a record where employee_id is #ID_300002. Only one row matches the condition in the WHERE clause, so only one row is affected.
Delete multiple rows
This example shows how MySQL can delete multiple rows in a single statement.
MySQL will delete all rows that matche the conditions specified in the WHERE clause.
For example, if you want to delete rows where the department column is "IT," you can write a SQL query like this:
DELETE FROM employees
WHERE department = "IT";
This SQL query will delete two rows from the "employees" table.
Summary
In this tutorial, you have learned how to delete one or more rows from an existing table in a database using the DELETE statement. MySQL will only delete rows that meet the specified criteria.