mysql - 如何在具有多个连接和条件的查询中从单个表中删除记录？

Question

具有以下相关表

我需要消除 和 之间的关系table4，即在table4与table3不同的情况下table1存在的数据table1_table4targettarget

为了确定受影响的行数，我编写了这个查询

SELECT 
    COUNT(*)
FROM
    table2
        INNER JOIN
    table2_table3 ON table2.id = table2_table3.table2_id
        INNER JOIN
    table3 ON table3.id = table2_table3.table3_id
        INNER JOIN
    table1_table2 ON table2.id = table1_table2.table2_id
        INNER JOIN
    table1 ON table1.id = table1_table2.table1_id
        INNER JOIN
    table1_table4 ON table1.id = table1_table4.table1_id
        INNER JOIN
    table4 ON table4.id = table1_table4.table4_id
WHERE
    table3.target != table4.target;

在这里，我确定了 149 条受影响的记录。

现在我需要删除table1_table4. 你能告诉我我应该如何写这个删除查询吗？

提前致谢

标签： mysql

Answer 1

您可以使用与您的选择相同的连接

    DELETE t2.* 
    FROM table2 t2
    INNER JOIN
        table2_table3 ON table2.id = table2_table3.table2_id
            INNER JOIN
        table3 ON table3.id = table2_table3.table3_id
            INNER JOIN
        table1_table2 ON table2.id = table1_table2.table2_id
            INNER JOIN
        table1 ON table1.id = table1_table2.table1_id
            INNER JOIN
        table1_table4 ON table1.id = table1_table4.table1_id
            INNER JOIN
        table4 ON table4.id = table1_table4.table4_id
    WHERE     table3.target != table4.target;

对于 table1_table4 ，如果连接条件相同，则可以使用

    DELETE table1_table4.*
    FROM table1_table4 
    INNER JOIN
        table2_table3 ON table2.id = table2_table3.table2_id
            INNER JOIN
        table3 ON table3.id = table2_table3.table3_id
            INNER JOIN
        table1_table2 ON table2.id = table1_table2.table2_id
            INNER JOIN
        table1 ON table1.id = table1_table2.table1_id
            INNER JOIN
        table1_table4 ON table1.id = table1_table4.table1_id
            INNER JOIN
        table4 ON table4.id = table1_table4.table4_id
    WHERE     table3.target != table4.target;