MySQL DELETE Statement across Multiple Tables Tutorial

Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function. The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.
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]

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.
where_condition is an expression that evaluates to true for each row to be deleted.
Important facts:

• Currently, you cannot delete from a table and select from the same table in a subquery.
• You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.
• DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table, TRUNCATE TABLE cannot be used whereas DELETE can.
• If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is reused later for a BDB table, but not for a MyISAM or InnoDB table.
• If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables.
• The time required to delete individual rows is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable.
• To delete all rows from a table, TRUNCATE TABLE tbl_name is faster than than DELETE FROM tbl_name. Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

The DELETE statement supports the following modifiers:

• If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE).
• For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.
• The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings.

Examples:
DELETE FROM user WHERE name = ‘Veer’;
This query will delete all the rows from the table user.
DELETE FROM user WHERE name = ‘Veer’ LIMIT 4;
Normally, MySQL makes no guarantees about which four records selected by the WHERE clause it will delete. An ORDER BY clause in conjunction with LIMIT provides better control.
DELETE FROM user WHERE name = ‘Veer’ ORDER BY id LIMIT 4;
This will delete the first four record from the table sorted in the ascending order of id field. That means it leaves the records which has higher id value.
Multiple-table DELETE statements can be written in two formats. For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching. The following example demonstrates one syntax, for a query that deletes rows from a table t1 where the id values match those in a table t2:
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
The second syntax is slightly different:
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
To delete the matching records from both tables, the statements are:

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

Or
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;
Delete statement using JOINS

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

Or

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

Above both statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

Above statement delete rows that exist in t1 that have no match in t2.
In DELETE statement you can use the alias also like this:

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2

WHERE a1.id=a2.id;

DELETE Statement using a group by function and on single table.

DELETE FROM employee USING employee INNER JOIN (SELECT employee_id

FROM employee

GROUP BY employee_id

HAVING COUNT(*)>1) AS emp USING (employee_id);
DELETE statement can work with multiple joins

DELETE a1, a2, a3, a4, a5

FROM atom AS a1

LEFT JOIN atom_desc AS a2 ON a2.desc_id = a1.id

LEFT JOIN atom_review AS a3 ON a3.review_id = a2.id

LEFT JOIN atom_content AS a4 ON a4.content_id = a1.id

LEFT JOIN atom_history AS a5 ON a5.history_id = a4.id

WHERE a1.name = ‘Veer’;