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
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;