Update junction table in many-to-many relationship

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update junction table in many-to-many relationship

Author

Topic

Philthy
Starting Member

2 Posts

Posted - 2009-02-26 : 09:17:11

How would you write the UPDATE statement, when you want to update a junction table in many-to-many relationship? The table has two fields, one for each of the primary keys in the tables it links together. A classic many-to-many relationship junction table I guess.

I can insert and delete from all the tables, and I can select from each of the linked tables fine, so I believe my tables are designed correctly. But I can't make the update work in the junction table. I need to use both fields as a combined PK in the UPDATE statement, right?

I can't get the syntax right..

Anyone have anything lying around?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-26 : 09:20:56

Post sample data and output.

Philthy
Starting Member

2 Posts

Posted - 2009-02-26 : 13:33:34

I have this blog like application, and I want to associate tags with the posts, you know, like any other blog.
I set up this many-to-many relationship in my database, and the tables are: tags, articles and tag_articles, the latter being the junction table.

The junction table has three fields, TagID (PK from tags), ArticleID (PK from articles) and TagName, which is just so I can see the tags names easier.

TagID and ArticleID in the junction table, form the PK for that table.

I’m using stored procedures. When I fill out a post, I check the tags I want for it in a CheckBoxList. Then I run a SP that inserts the post, and then another that inserts the three fields in the junction table. For instance, I could insert three tags TagID 21, 22 and 23 for ArticleID 42. This works fine, and I can select from both tags and articles. The InsertArticleTags SP would then run three times.

When I try to edit a post, and select other tags, the tag_articles isn’t updated properly. So far, the SP for updating that table looks like this:

ALTER PROCEDURE dbo.blog_Articles_Update_Articles_Tags_test
(
@ArticleID int,
@TagID int,
@TagName nvarchar(250)
)

AS

declare @temp int
select @temp = TagID
from Blog_Articles_Tags
where ArticleID = @ArticleID

update Blog_Articles_Tags
set TagID = @TagID,
TagName = @TagName,
ArticleID = @ArticleID

WHERE Blog_Articles_Tags.ArticleID = @ArticleID and Blog_Articles_Tags.TagID = @temp

Let’s say I want to change the tags for the above ArticleID 42 to TagID 30, 31 and 32, the UpdateArticleTags SP runs three times, but it inserts the same TagID three times. In this case, the foreign key fields in the junction table are not set to one combined PK. If I set the two foreign keys to one PK, I get an error that says I can’t insert duplicate values, which is logical.

I believe that, in the update where clause, I need to use both the @ArticleID, AND the existing TagID’s, so I tried to first save the existing TagID in a variable and then use it in the where clause with the @ArticleID. This would create a combination of the two values that would be unique in the table, right? However, it doesn’t work, and I am running low on ideas….

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources