MySQL - DELETE and TRUNCATE TABLE

mysql

How can we use the DELETE statement?

DELETE FROM customer WHERE id=1005;

When should we use TRUNCATE TABLE and when should we use DELETE FROM TABLE without a WHERE clause?

The word TABLE in TRUNCATE TABLE is optional. To remove only specific records in a table, TRUNCATE TABLE cannot be used. You must use DELETE with a WHERE clause. When you omit the WHERE clause from DELETE, it is logically equivalent to TRUNCATE TABLE, but there are operational differences.

If you need to know how may 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 also happens for DELETE statement that does not include WHERE clause. If this side effect is undesirable, use a WHERE clause that always evaluates to true:

DELETE FROM table_name WHERE 1;

The present of the WHERE clause cause MySQL to evaluate it for each row, so the effect of the WHERE clause is to produce a row-by-row table-emptying operation. This form of DELETE (with a WHERE clause that always evaluate to true) avoid the side effect of resetting AUTO_INCREMENT sequence, but execute much more slowly than a DELETE with no WHERE.

How can we use the TRUNCATE TABLE statement?

TRUNCATE TABLE table_name;

Can we use the ORDER BY and LIMIT clauses with the DELETE statement?

Yes. We can use the ORDER BY and LIMIT with the DELETE statement to provide better control.

How can we delete records from multiple tables with one DELETE statement?

See the "How can we delete data from one table using information from another table" question on this page.

How can we delete data from one table using information from another table?

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. Such statements can be used to perform the following operations:

  1. Update rows in one table by transferring information from another table.
  2. Update / delete rows in one table, determining which rows to update / delete by referring to another table.
  3. Update / delete rows in multiple tables with a single statement.

Multi-table DELETE statements can be written in two formats:

DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

To delete matching records from both tables:

DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;

The ORDER BY and LIMIT clauses normally supported by UPDATE and DELETE aren't allowed when these statements are used for multiple-table operations.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License