删除自关联表下的所有数据

<update id="deleteConfig">
    WITH RECURSIVE DeleteHierarchy AS (
    SELECT id, parent_id
    FROM fsd_risk_analysis_config
    WHERE id IN
    <foreach collection="ids" open="(" close=")"  item="id" separator=",">
        #{id}
    </foreach>

    UNION ALL

    SELECT t.id, t.parent_id
    FROM fsd_risk_analysis_config t
    JOIN DeleteHierarchy d ON t.parent_id = d.id
    )
    update fsd_risk_analysis_config set is_deleted=1
    WHERE id IN (SELECT id FROM DeleteHierarchy);
</update>

WITH RECURSIVE DeleteHierarchy AS (
? SELECT id, parent_id
? FROM your_table_name
? WHERE id = 2

? UNION ALL

? SELECT t.id, t.parent_id
? FROM your_table_name t
? JOIN DeleteHierarchy d ON t.parent_id = d.id
)
DELETE?
FROM
?? ?your_table_name?
WHERE
?? ?id IN ( SELECT id FROM DeleteHierarchy );

WITH RECURSIVE DeleteHierarchy AS (
? SELECT id, parent_id
? FROM your_table_name
? WHERE id IN (1, 2, 3) -- 替换为你的ID列表

? UNION ALL

? SELECT t.id, t.parent_id
? FROM your_table_name t
? JOIN DeleteHierarchy d ON t.parent_id = d.id
)
DELETE FROM your_table_name
WHERE id IN (SELECT id FROM DeleteHierarchy);