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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

FROM    table
    FROM    feed
    WHERE = )
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 '14 at 14:47

Your Answer


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.