11.5. The DELETE and trUNCATE TABLE Statements

11.5. The DELETE and trUNCATE TABLE Statements

To remove records from tables, use a DELETE statement or a trUNCATE TABLE statement. The DELETE statement allows a WHERE clause that identifies which records to remove, whereas TRUNCATE TABLE always removes all records. DELETE therefore can be more precise in its effect.

To empty a table entirely by deleting all its records, you can use either of the following statements:

 DELETE FROM table_name; TRUNCATE TABLE table_name; 

The word TABLE in TRUNCATE TABLE is optional.

To remove only specific records in a table, trUNCATE TABLE cannot be used. You must issue a DELETE statement that includes a WHERE clause that identifies which records to remove:

 DELETE FROM table_name WHERE ... ; 

When you omit the WHERE clause from a DELETE statement, it's logically equivalent to a TRUNCATE TABLE statement in its effect, but there is an operational difference: If you need to know how many records were deleted, DELETE returns a true row count, but TRUNCATE TABLE returns 0.

If a table contains an AUTO_INCREMENT column, emptying it completely with TRUNCATE TABLE might have the side effect of resetting the sequence. This may also happen for a DELETE statement that includes no WHERE clause. Resetting the sequence causes the next record inserted into the table to be assigned an AUTO_INCREMENT value of 1. If this side effect is undesirable when emptying the table, use a WHERE clause that always evaluates to true:

 DELETE FROM table_name WHERE 1; 

The presence of the WHERE clause in this statement causes MySQL to evaluate it for each row. The expression 1 is always true, so the effect of the WHERE clause is to produce a row-by-row table-emptying operation. Note that although this form of DELETE avoids the side effect of resetting the AUTO_INCREMENT sequence when performing a complete-table deletion, the disadvantage is that the statement executes much more slowly than a DELETE with no WHERE.

The following comparison summarizes the differences between DELETE and TRUNCATE TABLE:

DELETE:

• Can delete specific rows from a table if a WHERE clause is included

• Usually executes more slowly

• Returns a true row count indicating the number of records deleted

trUNCATE TABLE:

• Cannot delete just certain rows from a table; always completely empties it

• Usually executes more quickly

• Returns a row count of zero rather than the actual number of records deleted

11.5.1. Using DELETE with ORDER BY and LIMIT

DELETE supports ORDER BY and LIMIT clauses, which provides finer control over the way records are deleted. For example, LIMIT can be useful if you want to remove only some instances of a given set of records. Suppose that the people table contains five records where the name column equals 'Emily'. If you want only one such record, use the following statement to remove four of the duplicated records:

 DELETE FROM people WHERE name = 'Emily' LIMIT 4; 

Normally, MySQL makes no guarantees about which four of the five records selected by the WHERE clause it will delete. An ORDER BY clause in conjunction with LIMIT provides better control. For example, to delete four of the records containing 'Emily' but leave the one with the lowest id value, use ORDER BY and LIMIT together as follows:

 DELETE FROM people WHERE name = 'Emily' ORDER BY id DESC LIMIT 4; 

11.5.2. Multiple-Table DELETE Statements

DELETE supports a multiple-table syntax that enables you to delete records from a table based on the contents of another table. This syntax also allows records to be deleted from multiple tables simultaneously. The syntax has much in common with that used for writing multiple-table SELECT statements, so it's discussed in Section 12.5, "Multiple-Table UPDATE and DELETE Statements."