MySQL DELETE usages and examples

In MySQL, a DELETE statement is used to delete rows that meet specified conditions from a table.

DELETE syntax of deleting rows from one table

You can use one DELETE statement to delete rows from one or multiple tables. Here is the syntax of DELETE for deleting rows from one table:

DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

Here:

• The table name which table you delete rows from is followed by DELETE FROM keyword.
• The WHERE clause is used to filter the rows that need to be deleted. The WHERE clause is optional. If you did not specify a WHERE, all rows will be deleted from the table.
• The ORDER BY Clause is used to specify the order of deleting rows. It is optional.
• The LIMIT clause is used to specify the maximum number of rows to be deleted. It is optional.
• The DELETE statement returns the number of rows deleted.

The WHERE clause in a DELETE statement is very important. you shoud not omit WHERE clauses in most cases.

DELETE examples

In the following example, we use the actor table from Sakila sample database as a demonstration.

Let’s create a table actor_copy as a copy of the actor table:

CREATE TABLE actor_copy AS (SELECT * FROM actor);

Query OK, 201 rows affected (0.01 sec)
Records: 201  Duplicates: 0  Warnings: 0

Let’s try our example now.

Delete rows

• Delete the row which actor_id equals 1

  DELETE FROM actor_copy WHERE actor_id = 1;
  

  Query OK, 1 row affected (0.00 sec)
  

• Delete rows which last_name equal KILMER

  DELETE FROM actor_copy WHERE last_name = 'KILMER';
  

  Query OK, 5 rows affected (0.01 sec)
  

Limit the number of rows

Consider these requirements:

• Delete the 5 latest rows
• Delete the 10 newly registered users

You can combine ORDER BY and LIMIT clauses.

The following statement deletes the max 10 actor_id rows from actor_copy:

DELETE FROM actor_copy
ORDER BY actor_id DESC
LIMIT 10;

Query OK, 10 rows affected (0.01 sec)

In most cases, you shuold use a LIMIT clause and a ORDER BY clause together in a DELETE statement.

Delete all rows from a table

If there is not a WHERE or LIMIT clause in a DELETE statement, all rows in the table will be deleted.

DELETE FROM actor_copy;

Query OK, 185 rows affected (0.00 sec)

We use the following statement to check whether there are any rows in the table:

SELECT COUNT(*) FROM actor_copy;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Now, the actor_copy table is empty.

If you just want to clear the table, you can use the TRUNCATE TABLE statement to get better performance. as follows:

TRUNCATE actor_copy;

Table alias in DELETE

In earlier versions of MySQL, single-table DELETE statement does not support table alias. for example:

DELETE FROM main_table m
WHERE NOT EXISTS (
    SELECT *
    FROM another_table a
    WHERE a.main_id = m.id
  );

It will generate an error: Error Code: 1064\. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't' at line 1 0.016 sec .

You can use the following statement instead:

DELETE FROM main_table
WHERE NOT EXISTS (
    SELECT *
    FROM another_table a
    WHERE a.main_id = main_table.id
  );

Multi-table DELETE

You can also specify more than one table in a DELETE statement to delete one or more rows in multiple tables.

• The following statement deletes the rows from the t1 and t2 tables:

  DELETE t1, t2
  FROM t1 INNER JOIN t2
  WHERE t1.id = t2.id;
  

• The following statement deletes the rows from the t1 table:

  DELETE t1
  FROM t1 INNER JOIN t2
  WHERE t1.id = t2.id;
  

• The following statement uses LEFT JOIN in a DELETE statement:

  DELETE t1
  FROM
    t1 LEFT JOIN t2 ON t1.id = t2.id
  WHERE t2.id IS NULL;
  

DELETE modifier

In MySQL, DELETE statements support 3 modifiers:

• LOW_PRIORITY : If you specify LOW_PRIORITY modifier, MySQL server will delay the execution of the DELETE operation until there are no clients there are no clients who read on the table.

  LOW_PRIORITY modifier is supported by those storage engines which only has table-level locking, such as: MyISAM, MEMORY, and MERGE.

• QUICK : If you specify a QUICK qualifier, the MyISAM storage engine does not DELETE merge index during operation.

• IGNORE : If you specify a IGNORE modifier, MySQL server will perform DELETE ignore those errors can be ignored during the operation. These errors return as WARNING.

Here is the usage of modifiers:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name

Conclusion

In this article, you learned how to remove rows from a table using MySQL DELETE statement. The following are the main points of the DELETE statement:

• DELETE statements can be used to delete rows from one table or multiple tables.
• The WHERE clause is used to filter the rows that need to be deleted. The WHERE clause is optional. If you did not specify a WHERE, all rows will be deleted from the table.
• The ORDER BY Clause is used to specify the order of deleting rows. It is optional.
• The LIMIT clause is used to specify the maximum number of rows to be deleted. It is optional.
• The DELETE statement returns the number of rows deleted.
• Multi-table deleting needs JOIN to connect multiple tables.
• If you want to ignore mistakes during the delete operation, you can use IGNORE modifier.