Add and/or delete records from SQL DB

Question

Good day everyone, I have a situation where there is a selectable list of items on a website where each selected item has a record in a mySQL table:

parentID BIGINT NOT NULL
childID BIGINT NOT NULL

The 'parentID' is the ID of the record that the 'childID' belongs to, so the DB may contain something like:

2 | 13
4 | 8
4 | 10
4 | 7

This would indicate that the #4 record has three records associated with it (7,8,10). Now let's say that the user selects another item in the list on the website (e.g. record 9), but also unselects record 7, what is the best practice to delete record 7 from the list while adding record 9? I've looked at using 'NOT IN' statements (without success) and 'NOT EXIST' statements (but didn't implement), but figured I'd ask here before spending too much time on something that can be done much better than my own design.

Dave

Answer 1

You could write application logic to detect when a row is being removed while another being added, and use that with an UPDATE query to modify the "deleted" row into the "added" row. But frankly this is one of those cases where the apparent optimizations are way overshadowed by the complexity (what happens if two rows are being deleted but only one added? or vice-versa?), and the result would be an unmaintainable nightmare.

Instead, I would recommend two passes -- one that DELETEs removed records, and a second that INSERTs new ones.

Your DELETE query could look like this: DELETE FROM tableName WHERE parentID=4 AND childID NOT IN (8,10,9), where 8, 10, and 9 are the record IDs passed in to your app by your form. You'll notice that childID 9 is in the query, but not in the database; that's fine, it will cause no harm, and is just a side effect of the fact that gluing all the passed IDs together into a single query is the simplest option. (Note that this is just one option; another option would be to first SELECT the existing childIDs, then compare that with the list of passed-in IDs from your form and just DELETE the ones that have been removed.)

Your INSERT pass would then loop through the passed-in IDs from your form and INSERT the ones that aren't in the database. This would likely require a SELECT first to find the IDs that are there, and compare that with what's passed in to find the ones that aren't already in the database. I'm not going to go too much further with this because it seems you've already got functioning logic to add rows; I can elaborate further if that's not the case, however.

In either case, be absolutely certain that you are sanitizing your inputs to guard against SQL injection! See this question and answers for more on that topic.