如何基于多表聚合查询结果删除关联表数据，解决MySQL 1093错误

你使用的MariaDB 5.5.65版本继承自MySQL 5.5内核，该版本的限制是：不允许在UPDATE/DELETE语句的子查询中直接引用要操作的目标表，因此你直接在IN条件的子句中查询persons_linked_companies表时触发1093错误。

最简单的修改方式是给内层的子查询额外套一层临时派生表，让数据库将其识别为独立的临时结果集，不会判定为直接引用目标表：

DELETE pcld 
FROM persons_linked_companies as pcld
WHERE pcld.id IN(
    SELECT id FROM (
        SELECT 
            id
        FROM
            persons_linked_companies pcl
        WHERE
            companies_id = (SELECT 
                    c.id
                FROM
                    companies c
                    INNER JOIN entities e ON e.company_id = c.id
                    INNER JOIN persons p ON p.entity_id = e.id
                WHERE
                    p.id = pcl.persons_id)
    ) as tmp
)

相比子查询套层的方案，直接使用多表关联DELETE的写法性能更好，也不会触发版本限制：

DELETE pcld
FROM persons_linked_companies pcld
INNER JOIN persons_linked_companies pcl ON pcld.id = pcl.id
INNER JOIN persons p ON p.id = pcl.persons_id
INNER JOIN entities e ON e.id = p.entity_id
INNER JOIN companies c ON c.id = e.company_id
WHERE pcl.companies_id = c.id

内容的提问来源于stack exchange，提问作者donmelchior