Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large daily data feed which can contain up to around 5,000 ID's. I need to delete all pre-existing records in the DB that are not referenced in the new feed. Should I create a temporary table for these 5,000 ID's, and run a LEFT JOIN IF NULL DELETE statement? Or, is it all the same if I just pass the ID's over and run a DELETE IF NOT IN statement. My assumption is that MySQL would do the work of creating the temp table for me when using IN statements.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

With a large set you may want to use an NOT EXISTS clause rather than a NOT IN clause. It will help if you index the id on your feed table. Using an index will allow the optimizer to use the index rather than reading the table. Try a query like:

DELETE
FROM    table
WHERE NOT EXISTS (
    SELECT  feed.id
    FROM    feed
    WHERE   feed.id = table.id )
share|improve this answer
    
Thank you BillThor, your answer was the tipping point in my decision to rebuild my entire process. When building a large import feed like mine, I believe a staging table is the only way to go, particularly if you want to run batch queries. Otherwise, you'll require mountains of application code to accomplish what you can with just a little SQL –  Brandon Buster Jul 25 at 14:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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