如果没有NULL值，如何删除内部/左连接的mysql数据库中的行

Question

I've got one table which looks like this:

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

and the second table looks like this:

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

How can I delete the values from table1 and table2 (with one single query) only if there will not be any valid join. e.g. delete from table1 the row with id_1=3 and from table2 the row with id_2=5 but NOT the row in table1 with id_1=1 because there are still entries in table2 which can be joined with.

I tried the following, but it will not work:

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)

How can I change my query or which FASTER query can be choosen for my intention

Answer 1

I'm not sure there's a way to do this in one query. This comes close:

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)

but it doesn't delete id_2 = 2 from Table2.

I think it may need to be done in two queries: First delete all rows in Table1 that don't join with any outdated = 0 rows in Table2, then delete all the outdated rows in 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;

Or you can reverse the order, then you don't need to check b.outdated at all:

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;