The project I'm working on currently has a categories table, as well as a business_category table (an association between a business and its categories). I want to run a query that will update the values in business_category with the new associations, and keep the ones that exist currently.
I know I can do a
DELETE statement to first clear the associations, then do an insert. My question is, is there a better way to do it? Is this performance-savvy? I imagine this query might be called somewhat often, and it seems a bit extreme to delete and re-insert every time it's ran, when really all I want to do is insert if the record doesn't exist already.
For table structure,
business_category is just two columns:
Anybody got any ideas? Should I just go ahead and do the delete? Or is there a better way?
Thanks in advance.
Best How To :
There are two options I see, both of which should be more performant that always deleting everything and then inserting the updated data.
1) First select all category_id's for the business_id being updated.
2) From the list determine which category_id's need to be removed, and only delete those.
3) From the list determine which category_id's need to be added, and only add those.
4) Anything that is left is the same, so it doesn't need to be touched.
Or you can:
1) Run an INSERT query with a "ON DUPLICATE KEY UPDATE category_id=category_id" (Here's some docs about it)
2) Run a delete query where any rows for that business_id, and where the category_id's are NOT in the list of updated category_id's. This will remove any existing ones that are not in the new updated list. ("DELETE .. WHERE category_id NOT IN ($list_of_categories)")
In the end you basically want to reduce how much writing you have to do because each time you write the index on the table will need to be updated. Doing a large amount of writes will be slower than doing a read and only writing what you have to.
Hope that helps