boo_lolly

Unfortunately the second query does not work because MySQL will not delete from a table that you are performing joins on in a SELECT sub-query. For example DELETE FROM food_tags WHERE food_id IN ( SELECT foods.id FROM food_tags JOIN foods ON foods.id = food_tags.food_id JOIN tags ON tags.id = food_tags.tag_id WHERE food_id NOT IN ( SELECT MIN(id) FROM foods GROUP BY name ) ) This does not work. Error message: You can't specify target table 'food_tags' for update in FROM clause Also it doesn't alleviate the fact that it has 2 SELECT sub-queries which I'm trying to avoid if at all possible for performance reasons.

@Psycho, Thanks for your quick reply! Your first query does what it says, but not what I need. The issue is that we cannot simply grab all that don't match. We must grab records from the food_tags table that have a specific set of food ids. Otherwise we would be deleting several records for several other foods based on additional criteria from the food_tags table which we do not want to do. It must be a specific set of food ids. Hence the initial SELECT * FROM `food_tags` WHERE `food_id` IN (... Think of it like narrowing down the food ids list rather than simply grabbing all that don't match a specific criteria. However, I believe your second query does in fact grab a specific list of only matching food ids. I will test that out and see if this is the case. In response to your initial question - the goal is the migrate the database schema incrementally to accommodate this.

In short, I have have a table `foods` that has duplicates on occasion. I have another associated table `food_tags` that has tags associated with the foods listed in the `foods` table. **Objective:** I want to retrieve the records from the `food_tags` table for all duplicates found in the `foods` table except for the *first* records in the `foods` table (the records with the lowest `id` value). SQL Fiddle - http://sqlfiddle.com/#!9/ee1c6/2 I have a working example of what I want but I'm almost certain this is not the best way to do it. Here's what I have: SELECT * FROM `food_tags` WHERE `food_id` IN ( SELECT `id` FROM `foods` WHERE `id` NOT IN ( SELECT MIN(`id`) FROM `foods` GROUP BY `name` ) ) Basically I want to exclude the first record of the duplicates in the `foods` table (the one with the lowest `id`), but retrieve all the remaining duplicates and then get the results from the `food_tags` table based on the `food_id` of the duplicates. I'm very curious to see what your ideas are on how to solve this problem and the best way to approach it based on the constraints. **Additional Information:** Ultimately the goal is to delete these records, along with other records in other tables based on the same criteria. I realize that there are ways you can setup the schema or normalize the database to handle duplicate entries and automatic deletions of records from one table based on deletions in another related table. For the sake of the issue, let's assume that these options are not available. Thanks in advance for your time and insight.