Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Updating a join table

9:55 pm on Jan 3, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0

I am using a join table to record which category an event belongs to.

category_id | event_id
5 | 1
1 | 1
2 | 3
6 | 4
7 | 4

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.

7:01 am on Jan 16, 2011 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
votes: 12

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.

- John
9:25 am on Jan 16, 2011 (gmt 0)

Full Member

joined:Sept 29, 2005
votes: 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.
12:59 pm on Jan 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0

Thanks for the replies.

johnblack is 100% on the mark and it was the increase in the number of categories for a specfic event that I was thinking about. I decided to delete all then reinsert.
2:03 am on Jan 19, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 9, 2003
votes: 0

Wrap the delete and re-insert into a transaction, and the outside world will never know the difference.