'How can I delete rows from table A and table B if rows in table B don't exist

I have 2 tables and I need to delete rows from both tables if A.itemID does not exist in table B
I've tried doing :
DELETE a,b FROM A a, B b WHERE NOT EXISTS (SELECT * FROM B b WHERE b.cid= a.itemID );
But I cant get the error: Error Code: 1093. You can't specify target table 'b' for update in FROM clause

All help is appreciated!

I have the following tables : 

Table A 
+--------+--------+-------------+
|catId   | itemID | lastModified|
+--------+--------+-------------+
| 167262 | 678909 | 2017-10-01  |
| 167262 | 986785 | 2012-01-03  |
| 143210 | 456776 | 2018-04-30  |
| 143210 | 345676 | 2019-06-14  |
| 143210 | 010836 | 2016-03-09  |
| 379588 | 883567 | 2019-03-04  |
+--------+--------+-------------+
Table B 
+--------+----------+-------+
| cid    |locationid| Type  |
+--------+----------+-------+
| 678909 | 1        | a     |
| 986785 | 1        | a     |
| 143210 | 2        | b     |
| 883567 | 3        | a     |
+--------+----------+-------+ ```


My resulting tables would be :

Table A 
+--------+--------+-------------+
|catId   | itemID | lastModified|
+--------+--------+-------------+
| 167262 | 678909 | 2017-10-01  |
| 167262 | 986785 | 2012-01-03  |
| 379588 | 883567 | 2019-03-04  |

Table B
+--------+----------+-------+
| cid    |locationid| Type  |
+--------+----------+-------+
| 678909 | 1        | a     |
| 986785 | 1        | a     |
| 883567 | 3        | a     |
+--------+----------+-------+
mysqlsqljoindelete-row


Solution 1:[1]

I need to delete rows from both tables if A.itemID does not exist in table B

If the itemId does not match, then there is no row in B. So, you just need to delete in A. So:

DELETE a FROM a
    WHERE NOT EXISTS (SELECT 1
                      FROM B b
                      WHERE b.cid = a.itemID
                     );

Solution 2:[2]

You could also express your logic using a delete anti-join:

DELETE a
FROM A a
LEFT JOIN B b ON b.cid = a.itemID
WHERE b.cid IS NULL;

Solution 3:[3]

To add to Gordon Linoff's answer (which helped me get to my solution) the following shows how to view the records that will be deleted before doing it:

To view the records that WILL be affected:

SELECT TableA.ColumnName FROM TableA
WHERE NOT EXISTS 
(SELECT 1 FROM TableB WHERE TableB.ColumnName = TableA.ColumnName);

To then delete the affected rows:

DELETE TableA FROM TableA
WHERE NOT EXISTS 
(SELECT 1 FROM TableB WHERE TableB.ColumnName = TableA.ColumnName);

So, the only differences are change SELECT for DELETE and removing the ColumnName from after the SELECT word.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 Tim Biegeleisen
Solution 3 FlashTrev

Related Questions