How to Correctly Use Subqueries in MySQL DELETE Statements?

Subquery conditions in MySQL DELETE statement

You may encounter syntax errors when using a subquery as a condition to delete rows from a table. To solve this problem, you need to consider the limitations of using subqueries in DELETE operations.

In MySQL, you cannot use subqueries directly in the WHERE clause of a DELETE statement. However, there is a clever way around this limitation. By using an additional subquery to include the original subquery, the delete operation can be performed successfully.

For example, the following query:

<code class="language-sql">DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (
    SELECT DISTINCT(th1.tid)
    FROM term_hierarchy AS th1
    INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
    WHERE th1.parent = 1015
);</code>

will produce a syntax error. However, by wrapping the subquery in another subquery like this:

<code class="language-sql">DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
             FROM (SELECT id
                   FROM tableE
                   WHERE arg = 1 AND foo = 'bar') x);</code>

The delete operation can be performed successfully. Remember that the enclosing subquery should be named, as shown by the "x" alias in the example.

The above is the detailed content of How to Correctly Use Subqueries in MySQL DELETE Statements?. For more information, please follow other related articles on the PHP Chinese website!