MySQL多表关联DELETE语句使用LIMIT报错，求改写为查询语句方案

对应的SELECT验证语句 #

先把你的DELETE查询转换成SELECT，用来确认要删除的目标记录：

SELECT gur.*
FROM group_master gm
JOIN group_users gu ON gu.group_id = gm.id
JOIN group_user_roles gur ON gur.group_user_id = gu.id
WHERE gm.school_code = 14741359 AND gm.ayid = 3
LIMIT 1;

修正后的DELETE语句 #

MySQL不支持在多表DELETE的主语句末尾直接加LIMIT，需要通过子查询先筛选出有限的关联记录，再执行删除，以下是两种可行写法：

写法一：通过子查询获取要删除的记录ID

假设group_user_roles表的主键为id：

DELETE FROM group_user_roles
WHERE id IN (
    SELECT gur.id
    FROM group_master gm
    JOIN group_users gu ON gu.group_id = gm.id
    JOIN group_user_roles gur ON gur.group_user_id = gu.id
    WHERE gm.school_code = 14741359 AND gm.ayid = 3
    LIMIT 1
);

写法二：嵌套子查询关联删除

先筛选出符合条件的单条group_users记录，再关联删除对应的group_user_roles：

DELETE gur
FROM group_user_roles gur
JOIN (
    SELECT gu.id AS gu_id
    FROM group_master gm
    JOIN group_users gu ON gu.group_id = gm.id
    WHERE gm.school_code = 14741359 AND gm.ayid = 3
    LIMIT 1
) AS sub ON gur.group_user_id = sub.gu_id;

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