9. DELETE

show you how to use the DELETE statement to delete rows from one or more tables.

Summary: in this tutorial, you will learn how to use the MySQL DELETE statement to delete rows from a table and return the number of deleted rows.

Introduction to MySQL DELETE statement

The DELETE statement allows you to delete rows from a table and returns the number of deleted rows.

Here’s the basic syntax of the DELETE statement:

DELETE FROM table_name
WHERE condition;

In this syntax:

First, specify the table from which you delete data after the FROM keyword.
Second, specify a condition to determine which rows to delete in the WHEREclause.

The WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table:

DELETE FROM table_name;

Note that to delete data from multiple related tables, you use the DELETE JOINstatement.

When you need to remove all rows from a large table and don’t need to know the exact number of rows deleted, you should use the TRUNCATE TABLE statement for better performance.

In a table that has a foreign key constraint, when you delete rows from the parent table, MySQL automatically deletes the rows in the child table if the foreign key uses the ON DELETE CASCADE option.

MySQL DELETE statement examples

We’ll create a table called contacts with some sample data for the demonstration:

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

INSERT INTO contacts (first_name, last_name, email, phone)
VALUES
    ('John', 'Doe', 'john.doe@email.com', '123-456-7890'),
    ('Jane', 'Smith', 'jane.smith@email.com', '987-654-3210'),
    ('Alice', 'Doe', 'alice.doe@email.com', '555-123-4567'),
    ('Bob', 'Johnson', 'bob.johnson@email.com', '789-321-6540'),
    ('Eva', 'Doe', 'eva.doe@email.com', '111-222-3333'),
    ('Michael', 'Smith', 'michael.smith@email.com', '444-555-6666'),
    ('Sophia', 'Johnson', 'sophia.johnson@email.com', '777-888-9999'),
    ('Matthew', 'Doe', 'matthew.doe@email.com', '333-222-1111'),
    ('Olivia', 'Smith', 'olivia.smith@email.com', '999-888-7777'),
    ('Daniel', 'Johnson', 'daniel.johnson@email.com', '666-555-4444'),
    ('Emma', 'Doe', 'emma.doe@email.com', '222-333-4444'),
    ('William', 'Smith', 'william.smith@email.com', '888-999-0000'),
    ('Ava', 'Johnson', 'ava.johnson@email.com', '111-000-9999'),
    ('Liam', 'Doe', 'liam.doe@email.com', '444-777-3333'),
    ('Mia', 'Smith', 'mia.smith@email.com', '222-444-8888'),
    ('James', 'Johnson', 'james.johnson@email.com', '555-666-1111'),
    ('Grace', 'Doe', 'grace.doe@email.com', '777-222-8888'),
    ('Benjamin', 'Smith', 'benjamin.smith@email.com', '999-111-3333'),
    ('Chloe', 'Johnson', 'chloe.johnson@email.com', '111-444-7777'),
    ('Logan', 'Doe', 'logan.doe@email.com', '333-555-9999');

1) Delete a row example

The following example uses the DELETE statement to delete a single row from the contacts table:

DELETE FROM contacts
WHERE id = 1;

Output:

Query OK, 1 row affected (0.01 sec)Code language: CSS (css)

In this example, the DELETE statement deletes the row with id 1. Since the condition returns only one row, the DELETE statement deleted a single row.

The output indicates that one row was deleted.

2) Delete multiple rows example

The multiple rows that we want to delete needs to be firstly selected before we apply the delete function. The following statement retrieves the contacts with the last name Smith:

SELECT * FROM contacts
WHERE last_name = 'Smith';

Output:

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | email                    | phone        |
+----+------------+-----------+--------------------------+--------------+
|  2 | Jane       | Smith     | jane.smith@email.com     | 987-654-3210 |
|  6 | Michael    | Smith     | michael.smith@email.com  | 444-555-6666 |
|  9 | Olivia     | Smith     | olivia.smith@email.com   | 999-888-7777 |
| 12 | William    | Smith     | william.smith@email.com  | 888-999-0000 |
| 15 | Mia        | Smith     | mia.smith@email.com      | 222-444-8888 |
| 18 | Benjamin   | Smith     | benjamin.smith@email.com | 999-111-3333 |
+----+------------+-----------+--------------------------+--------------+
6 rows in set (0.00 sec)Code language: JavaScript (javascript)

It returns 6 rows.

To delete these 6 rows, you can use the following DELETE statement:

DELETE FROM contacts
WHERE last_name = 'Smith';

Output:

Query OK, 6 rows affected (0.01 sec)Code language: CSS (css)

The output indicates that 6 rows were deleted.

3) Using MySQL DELETE statement with LIMIT clause

The following statement retrieves the contacts and sorts them by first names:

SELECT * FROM contacts
ORDER BY first_name;

Output:

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | email                    | phone        |
+----+------------+-----------+--------------------------+--------------+
|  3 | Alice      | Doe       | alice.doe@email.com      | 555-123-4567 |
| 13 | Ava        | Johnson   | ava.johnson@email.com    | 111-000-9999 |
|  4 | Bob        | Johnson   | bob.johnson@email.com    | 789-321-6540 |
| 19 | Chloe      | Johnson   | chloe.johnson@email.com  | 111-444-7777 |
| 10 | Daniel     | Johnson   | daniel.johnson@email.com | 666-555-4444 |
| 11 | Emma       | Doe       | emma.doe@email.com       | 222-333-4444 |
|  5 | Eva        | Doe       | eva.doe@email.com        | 111-222-3333 |
| 17 | Grace      | Doe       | grace.doe@email.com      | 777-222-8888 |
| 16 | James      | Johnson   | james.johnson@email.com  | 555-666-1111 |
| 14 | Liam       | Doe       | liam.doe@email.com       | 444-777-3333 |
| 20 | Logan      | Doe       | logan.doe@email.com      | 333-555-9999 |
|  8 | Matthew    | Doe       | matthew.doe@email.com    | 333-222-1111 |
|  7 | Sophia     | Johnson   | sophia.johnson@email.com | 777-888-9999 |
+----+------------+-----------+--------------------------+--------------+
13 rows in set (0.00 sec)Code language: JavaScript (javascript)

To delete the first three rows, you can use the DELETE statement with the ORDER BYand LIMIT clauses:

DELETE FROM table_table
ORDER BY sort_expression
LIMIT row_count;

For example, the following example uses the DELETE statement to delete the first three contacts sorted by first names:

DELETE FROM contacts
ORDER BY first_name
LIMIT 3;

Output:

Query OK, 3 rows affected (0.00 sec)Code language: CSS (css)

4) Using MySQL DELETE statement to delete all rows

The following example uses the DELETE statement without a WHERE clause to delete all rows from the contacts table:

DELETE FROM contacts;

Output:

Query OK, 10 rows affected (0.01 sec)Code language: CSS (css)

The statement deleted all rows (10 rows) from the contacts table.

If you retrieve data from the contacts table, you’ll see an empty result set:

SELECT * FROM contacts;

Output:

Empty set (0.00 sec)

Summary

Use the DELETE statement to delete one or more rows from a table.
Use the DELETE statement without a WHERE clause to delete all rows from a table.
Use the DELETE statement with a LIMIT clause to delete several rows from a table.