How to Delete Rows in MySQL Using a Subquery Without Syntax Errors?

Cleverly solve the syntax error when MySQL subquery deletes rows

A syntax error may occur when using a subquery as a condition for deleting rows in MySQL. This error usually occurs when the target table of the delete operation (here term_hierarchy) is also referenced in the subquery.

To solve this problem, an alternative approach is needed. The following query provides an efficient workaround:

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

In this query, the subquery used to identify the tid values ​​to be deleted is wrapped in an additional subquery (here named th1). This allows MySQL to avoid using the DELETE table in both the WHERE IN and term_hierarchy clauses, thus solving the syntax error.

The above is the detailed content of How to Delete Rows in MySQL Using a Subquery Without Syntax Errors?. For more information, please follow other related articles on the PHP Chinese website!