I have two tables, 'Products' and 'Details'.

Products [ID, Name]
1, Link's Simple Syrup
2, Team Shirt
3, Animal DVD

Details [ID, Product_id, attribute_name, attribute_value]
1, 1, potency, "high"
2, 2, color, "blue"
3, 2, size, "large"
4, 2, words, "go team"
5, 3, title, "Animals with wide angle lenses"
6, 3, price, "1 million dollars"

So say 'Team Shirt' gets removed from the Products table, and now I need to clean up (remove) the 3 leftover Details it used. Basically I want to look at each record from the Detail table and see if it's product_id corresponds to an existing ID in the products table. If I can construct a SELECT statement that returns only these orphaned Detail records, then I can change select to DELETE and remove them. That's my thinking anyway, any help creating the SQL query to accomplish that or suggestions on a different approach altogether if necessary would be much appreciated.

pseudocode of the logic might be something like this:

For each record in Details{
    if post_id is a NULL ID in Products delete this record

Two ways to accomplish that:

DELETE FROM details WHERE product_id NOT IN (SELECT id FROM products)

However, it is recommended to use left join:

DELETE details FROM details LEFT JOIN products ON details.product_id=products.id WHERE products.id IS NULL
| improve this answer | |

You can use Jehad Keriaki's SQL which looks like it would work very nicely.

Alternatively depending on your DB and engine type you may be able to enforce referential integrity (which is what you are asking about) by strongly defining the relationship between tables and cascading deletes so as to ensure no orphan rows exist in the second table. You would need to ensure that table2 rows cannot exist without table1 rows as parent. If you are using an engine that allows this then you could have the engine do clean up for you, automatically.

Otherwise just go with Jehad Keriaki's SQL which, as I said, looks like it should do the job very well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.