MySQL DELETE statement

Last update on April 25 2024 09:03:18 (UTC/GMT +8 hours)

DELETE statement

DELETE statement is used to remove rows from a table.

Version: MySQL 5.6

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Explanation:

The DELETE statement deletes rows from table_name and returns the number of deleted rows. You can use ROW_COUNT() function to check the number of deleted rows.
The conditions in the WHERE clause (optional) identify which rows to delete.
Without WHERE clause, all rows are deleted.
If you specify the ORDER BY clause, the rows are deleted in specified order.
The LIMIT clause is used to place a limit on the number of rows that can be deleted. These clauses apply to single-table deletes, but not multi-table deletes.

Multiple-table syntax :

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or :

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Important points:

Privileges: To delete a record(s) from a table, the user must have the DELETE privilege on that particular table.
The TRUNCATE TABLE statement is a faster way to empty a table than a DELETE statement with no WHERE clause. Here is a detail discussion on the difference between DELETE and TRUNCATE statement.
Subqueries: Currently there is no option to delete from a table and select from the same table in a subquery.
As of MySQL 5.6.2, DELETE supports explicit partition selection using the PARTITION option, which takes a comma-separated list of the names of one or more partitions or subpartitions (or both) from which to select rows to be dropped. Partitions not included in the list are ignored. Given a partitioned table t with a partition named p0, executing the statement DELETE FROM t PARTITION (p0) has the same effect on the table as executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all rows in partition p0 are dropped.
Auto-Increment Columns : If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table.

Example: MySQL DELETE specific rows or records

The following statement will remove those records from the 'newcate' table which satisfies the condition 'cate_id' = 'CA002'.

Sample table: newcate

Code:


-- This SQL statement deletes rows from the newcate table where the cate_id is 'CA002'
DELETE FROM newcate 
-- Specifies the condition for which rows to delete
WHERE cate_id = 'CA002';

Explanation:

The purpose of this SQL code is to delete rows from the newcate table where the cate_id is 'CA002'.

DELETE FROM newcate: This line specifies the target table from which rows will be deleted, which is newcate.

WHERE cate_id = 'CA002': This line specifies the condition that determines which rows will be deleted. It ensures that only rows with a cate_id of 'CA002' will be removed.

Example: MySQL DELETE all rows or records

If not accompanied by any condition about which rows are to be removed, MySQL DELETE statement removes all records or rows from a table. The following statement will remove all the rows or records from 'Newcate' table.

Code:


-- This SQL statement deletes all rows from the Newcate table
DELETE FROM Newcate;

Explanation:

The purpose of this SQL code is to delete all rows from the Newcate table.

DELETE FROM Newcate: This line specifies the target table from which all rows will be deleted, which is Newcate.

Example: MySQL DELETE with ORDER BY for limited number of rows

ORDER BY and LIMIT keyword can be used with MySQL DELETE statement to remove only a given number of rows, where columns are sorted in a specific order. The ORDER BY clause sorts the columns in a specific order and the LIMIT keyword deletes only the number rows mentioned by the numeric value immediately followed by LIMIT keyword. See the following example:

Sample table : newauthor

Code:


-- This SQL statement attempts to delete rows from the newauthor table without specifying an order.
-- MySQL does not support the ORDER BY clause directly in DELETE statements.
DELETE FROM newauthor 
-- The ORDER BY clause here is invalid in the context of a DELETE statement and will generate an error.
ORDER BY country DESC 
-- The LIMIT clause is also invalid in the context of a DELETE statement and will generate an error.
LIMIT 2;

Explanation:

The purpose of this SQL code is to delete rows from the newauthor table. However, the attempt to specify ordering and limiting the number of rows to be deleted in a single DELETE statement is invalid in MySQL.

DELETE FROM newauthor: This line specifies the target table from which rows are intended to be deleted, which is newauthor.

ORDER BY country DESC: This line attempts to order the rows in descending order based on the country column. However, in MySQL, the ORDER BY clause cannot be directly used in conjunction with a DELETE statement. This clause will generate an error.

LIMIT 2: This line attempts to limit the number of rows to be deleted to 2. Similarly, in MySQL, the LIMIT clause cannot be directly used in conjunction with a DELETE statement. This clause will also generate an error.

The statement above will do the following -
1. order the rows of 'newauthor' table in descending order according to column 'country',
2. delete only two(2) rows for each 'country'.

Example: MySQL DELETE rows using subqueries with alias and EXISTS

A subquery can be used with MySQL DELETE statement. This is useful when you want to delete rows depending upon a complex condition.

If we want to remove records from 'newauthor' table with following conditions -
1. 'aut_id' of 'newauthor' table must exist in 'book_mast' table,
2. 'no_pages' of 'book_mast' table must be more than 300,
3. 'aut_id' of 'newauthor' and 'aut_id' of 'book_mast' must match,

then execute the following code.

Sample table: newauthor

Sample table: book_mast

Code:


-- This SQL statement deletes rows from the newauthor table based on a condition specified in a subquery.
DELETE FROM newauthor 
-- The EXISTS keyword checks for the existence of rows in the subquery.
WHERE EXISTS 
-- This subquery selects all rows from the book_mast table where the no_page column is greater than 300,
-- and where the aut_id column in newauthor matches the aut_id column in book_mast.
(SELECT * 
FROM  book_mast 
WHERE no_page > 300 
AND newauthor.aut_id = book_mast.aut_id);

Explanation:

The purpose of this SQL code is to delete rows from the newauthor table based on a condition specified in a subquery that involves a correlation with the book_mast table.

DELETE FROM newauthor: This line specifies the target table from which rows will be deleted, which is newauthor.

WHERE EXISTS: This line introduces a subquery using the EXISTS keyword. It checks for the existence of rows in the subquery result set.

(SELECT * FROM book_mast WHERE no_page > 300 AND newauthor.aut_id = book_mast.aut_id): This subquery selects all rows from the book_mast table where the no_page column is greater than 300, and where the aut_id column in newauthor matches the aut_id column in book_mast.

MySQL TRUNCATE table

MySQL TRUNCATE TABLE statement is used to delete all the rows from a table. Apparently, this is similar to 'DELETE FROM <TABLE NAME>' statement,but they are quite different.

Difference between TRUNCATE and DELETE

TRUNCATE	DELETE
It is a DDL command (i.e. a command used to define the database structure or schema) and once you have deleted the rows using it, you can not use the ROLLBACK to undo the task.	It is a DML command (i.e. a command used for managing data) and can be rolled back.
You can't use WHERE clause.	You can use WHERE clause.
Faster than DELETE.	Slower than TRUNCATE.

Syntax:

TRUNCATE table<table_name>

Where table_name indicates name of the table

Example: MySQL TRUNCATE table

The following MySQL statement will delete all the rows from the newauthor table.

Sample table: newauthor

Code:


-- This SQL statement truncates (empties) all data from the newauthor table.
TRUNCATE TABLE newauthor;

Explanation:

The purpose of this SQL code is to remove all data from the newauthor table, essentially resetting the table to its initial state.

TRUNCATE TABLE newauthor: This line specifies the target table to be truncated, which is newauthor.

Previous: MySQL Update
Next: MySQL basic select statement