Home » MySQL Basics » MySQL DELETE

MySQL DELETE

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;Code language: SQL (Structured Query Language) (sql)

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 WHERE clause.

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;Code language: SQL (Structured Query Language) (sql)

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

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', '[email protected]', '123-456-7890'),
    ('Jane', 'Smith', '[email protected]', '987-654-3210'),
    ('Alice', 'Doe', '[email protected]', '555-123-4567'),
    ('Bob', 'Johnson', '[email protected]', '789-321-6540'),
    ('Eva', 'Doe', '[email protected]', '111-222-3333'),
    ('Michael', 'Smith', '[email protected]', '444-555-6666'),
    ('Sophia', 'Johnson', '[email protected]', '777-888-9999'),
    ('Matthew', 'Doe', '[email protected]', '333-222-1111'),
    ('Olivia', 'Smith', '[email protected]', '999-888-7777'),
    ('Daniel', 'Johnson', '[email protected]', '666-555-4444'),
    ('Emma', 'Doe', '[email protected]', '222-333-4444'),
    ('William', 'Smith', '[email protected]', '888-999-0000'),
    ('Ava', 'Johnson', '[email protected]', '111-000-9999'),
    ('Liam', 'Doe', '[email protected]', '444-777-3333'),
    ('Mia', 'Smith', '[email protected]', '222-444-8888'),
    ('James', 'Johnson', '[email protected]', '555-666-1111'),
    ('Grace', 'Doe', '[email protected]', '777-222-8888'),
    ('Benjamin', 'Smith', '[email protected]', '999-111-3333'),
    ('Chloe', 'Johnson', '[email protected]', '111-444-7777'),
    ('Logan', 'Doe', '[email protected]', '333-555-9999');
Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 following statement retrieves the contacts with the last name Smith:

SELECT * FROM contacts 
WHERE last_name = 'Smith';Code language: JavaScript (javascript)

Output:

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | email                    | phone        |
+----+------------+-----------+--------------------------+--------------+
|  2 | Jane       | Smith     | [email protected]     | 987-654-3210 |
|  6 | Michael    | Smith     | [email protected]  | 444-555-6666 |
|  9 | Olivia     | Smith     | [email protected]   | 999-888-7777 |
| 12 | William    | Smith     | [email protected]  | 888-999-0000 |
| 15 | Mia        | Smith     | [email protected]      | 222-444-8888 |
| 18 | Benjamin   | Smith     | [email protected] | 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';Code language: JavaScript (javascript)

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;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | email                    | phone        |
+----+------------+-----------+--------------------------+--------------+
|  3 | Alice      | Doe       | [email protected]      | 555-123-4567 |
| 13 | Ava        | Johnson   | [email protected]    | 111-000-9999 |
|  4 | Bob        | Johnson   | [email protected]    | 789-321-6540 |
| 19 | Chloe      | Johnson   | [email protected]  | 111-444-7777 |
| 10 | Daniel     | Johnson   | [email protected] | 666-555-4444 |
| 11 | Emma       | Doe       | [email protected]       | 222-333-4444 |
|  5 | Eva        | Doe       | [email protected]        | 111-222-3333 |
| 17 | Grace      | Doe       | [email protected]      | 777-222-8888 |
| 16 | James      | Johnson   | [email protected]  | 555-666-1111 |
| 14 | Liam       | Doe       | [email protected]       | 444-777-3333 |
| 20 | Logan      | Doe       | [email protected]      | 333-555-9999 |
|  8 | Matthew    | Doe       | [email protected]    | 333-222-1111 |
|  7 | Sophia     | Johnson   | [email protected] | 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 BY and LIMIT clauses:

DELETE FROM table_table
ORDER BY sort_expression
LIMIT row_count;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

Empty set (0.00 sec)Code language: JavaScript (javascript)

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.
Was this tutorial helpful?
MySQL REPLACE
Previously
MySQL REPLACE
Up Next
MySQL DELETE JOIN
MySQL DELETE JOIN