MySQL DELETE statement
DELETE statement is used to remove rows from a table.
Version: MySQL 5.6
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [PARTITION (partition_name,...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
- 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]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]
- 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
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.
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
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
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
|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.|
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
TRUNCATE TABLE newauthor;
New Exercises:R Programming