Delete from a table where value doesn't exist in another table

Question

I've got a table (table_1) I'm trying to delete out of, based on if a value doesn't exist in another (table_2)

table_2 can have multiple entries based on the id from table_1, such as:

+----------+--------------+
|table_1_id|table_2_active|
+----------+--------------+
|1         |0             |
+----------+--------------+
|1         |1             |
+----------+--------------+
|2         |0             |
+----------+--------------+

To clarify, table_2 links table_1 and another table with other values, including whether or not that link is active, with a 0 for "inactive" and a 1 for "active".

I want to delete from table_1 (and consequently from table_2) where there are no "active" entries in table_2.

Started out with this:

DELETE t1, t2 FROM `table_1` t1
LEFT JOIN `table_2` t2 ON t1.id = t2.table_1_id
WHERE t2.active = 0

However this will delete things that have an "inactive" link, even if it still has an "active" one. For example, in the table above, I want to delete where table_1_id is 2, and not 1. I've also tried going into subqueries, but having issues getting the id from the main query to be used in the subquery, and I have to believe there's a way to do this without subqueries.

How do I delete from table_1 and table_2 where there are no table_2.active = 1?

Barmar · Accepted Answer · 2021-05-24 20:16:26Z

1

You need to invert the active test, and move it into the ON clause. Then check for a non-matching element in the WHERE clause.

DELETE t1 FROM `table_1` t1
LEFT JOIN `table_2` t2 ON t1.id = t2.table_1_id AND t2.active = 1
WHERE t2.table_1_id IS NULL

You only need to delete from table_1 here, since the LEFT JOIN won't include any existing rows from table_2. If table_1_id is declared as a foreign key with ON DELETE CASCADE, the related inactive rows in table_2 will be deleted automatically.

answered May 24, 2021 at 20:16

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sharf Over a year ago

Unfortunately, this is an old schema and there are no foreign keys. I'll give this a shot, thanks

Barmar Over a year ago

Then do two queries. After this query, delete everything from table_2 that isn't in table_1.

Collectives™ on Stack Overflow

Delete from a table where value doesn't exist in another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related