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

Question

有如下相关表

我需要消除table4和table1之间的关系，即table1_table4中存在的数据，条件是table4的target 与 table3 的 target

不同

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

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中受影响的数据。你能告诉我这个删除查询应该怎么写吗？

提前致谢

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;