I need to update this table if the event categories change and I have been trying to find a quick and simple method of updating the table with the new relationships without having to jump through too many loops.
I am thinking of deleting all the existing relationships based on event_id then inserting the new ones. Any suggestions on a better solution please.
Msg#: 4248721 posted 7:01 am on Jan 16, 2011 (gmt 0)
Unless I'm mistaken there is no way to execute a write query using a JOIN.
What I think you're trying to say is that you want to update items in two tables with minimal use of queries (since they are write queries). If true then what you'll need to do is create an array in your scripting language and build your UPDATE query for each table, one query per table. I'm not sure what your level of skill is so if you're not sure how to change multiple entries on the same table in a single query I recommend making a test table and practicing with phpMyAdmin which while it's code is messy and full of ticks and the spaces are in the wrong places should at least get you on track to figuring out how to better construct your queries.
Msg#: 4248721 posted 9:25 am on Jan 16, 2011 (gmt 0)
I think what he's asking is how to update a many-many relationship table, sometimes called a join table. In the example there would be a category and event table with a join table called category_event for example.
Other than deleting the existing records and inserting new ones as you suggest, there's no 'neat' way of doing it. The only thing I could think of is to update the existing records with the new category (assuming the input screen is event based). However what happens if the number of categories for the event changes? Then you still have to delete or insert records into the join table.
I think what you have come up with is the best way.