php - 仅当没有 NULL 值时，如何使用内/左连接删除 mysql 数据库中的行

Question

我有一张如下所示的表:

id_1 | col_1 | col_2
1    | 450   | 2018-01-30 21:38:00
2    | 111   | 2018-01-30 22:18:00
3    | 222   | 2018-01-31 22:18:00

第二个表如下所示:

id_2 | id_1 | beginn              | outdated    
1    | 1    | 2018-01-30 11:38:00 |  0
2    | 1    | 2018-01-30 12:18:00 |  1   <===== THIS ROW IS OUTDATED
3    | 1    | 2018-01-30 13:38:00 |  0
4    | 2    | 2018-01-30 14:18:00 |  0
5    | 3    | 2018-01-30 15:38:00 |  1   <===== THIS ROW IS OUTDATED
6    | 2    | 2018-01-30 16:18:00 |  0

仅当不存在任何有效联接时，如何从 table1 和 table2 中删除值(使用一个查询)。例如从 table1 中删除 id_1=3 的行，从 table2 中删除 id_2=5 的行，但不删除 table1 中 id_1=1 的行，因为 table2 中仍然有可以连接的条目。

我尝试了以下方法，但不起作用:

DELETE a,b FROM table1 a 
LEFT JOIN table2 b on a.id_1=b.id_1     
WHERE b.outdated=1 
AND NOT EXISTS (
    SELECT c.id_1 from table1 c 
    INNER JOIN table2 d on c.id_1=d.id_1 
    WHERE d.outdated=0)

如何更改我的查询或可以根据我的意图选择哪个更快的查询

Answer 1

我不确定是否有一种方法可以在一个查询中执行此操作。这很接近:

DELETE a, b
FROM Table1 AS a
LEFT JOIN Table2 AS b ON a.id_1 = b.id_1
LEFT JOIN Table2 AS c ON a.id_1 = c.id_1 AND c.outdated = 0
WHERE c.id_1 IS NULL
AND (b.id_1 IS NULL -- no match
    OR b.outdated = 1)

但它不会从 Table2 中删除 id_2 = 2。

我认为可能需要通过两个查询来完成:首先删除 Table1 中不与 中任何 outdated = 0 行连接的所有行Table2，然后删除Table2中所有过时的行。

DELETE a
FROM Table1 AS a
LEFT JOIN Table2 AS b ON a.id_1 = b.id_1 AND b.outdated = 0
WHERE b.id_1 IS NULL;

DELETE FROM Table2
WHERE outdated = 1;

或者您可以颠倒顺序，那么您根本不需要检查 b.outdated:

DELETE FROM Table2
WHERE outdated = 1;

DELETE a
FROM Table1 AS a
LEFT JOIN Table2 AS b ON a.id_1 = b.id_1
WHERE b.id_1 IS NULL;