MySQL DELETE statement

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:

DELETE FROM newcate 
WHERE cate_id='CA002';

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:

DELETE FROM 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:

DELETE FROM newauthor 
ORDER BY country DESC LIMIT  2;

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:

DELETE FROM newauthor 
WHERE exists 
(SELECT * 
FROM  book_mast 
WHERE no_page>300 
AND newauthor.aut_id=book_mast.aut_id);

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

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:

TRUNCATE TABLE newauthor;

Previous: MySQL Update
Next: MySQL basic select statement