Join and Update statement using junction table and sql

[jboyd111]

Hi, I've searched and haven't found a relevant answer to this 'simple' problem. I have 3 tables, one of which is a junction table between the other two: Users ----- UserId UserName Roles ----- RoleId RoleName UserRoles --------- UserRoleId UserId RoleId There are FK relationships between UserRoles and the other 2 tables. Problem statement: What I want to do is update the user's RoleId in the UserRoles table if I only have the person's RoleName and UserName. Here's what I'm starting with code-wise (MSSQL): update a set a.RoleId = ?? Set to what? FROM UserRoles a inner join Users b on a.UserId = b.UserId inner join Roles c on a.RoleId=c.RoleId where c.RoleName= 'Administrator' AND b.UserName='someuser' Further information: I'm trying to do this within an update method call within an ASP.NET GridView control. This information may be a bit more than what's needed but I thought I would mention it just in case it may offer a different solution. Thanks for any assistance in advance. --- Stan

[Luc Pattyn]

Sorry, I'm missing the logic in your question. This is what I read: we know the UserName (and hence the UserId) we know the RoleName (and hence the RoleId) I see two possibilities: 1. the UserRoles table does not contain a matching UserId,RoleId record; you have to insert one. 2. the UserRoles table does contain a matching UserId,RoleId record. Now what needs to be updated??? Or is it you want the existing UserId,RoleId record to get another value of UserRoleId for some mysterious reason? :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Season's Greetings to all CPians.

[jboyd111]

Your last point is what I'd like to find an answer for: I want to UPDATE the RoleId for an EXISTING RoleId,UserId combo. In short, I want to update the junction table.

[Luc Pattyn]

You failed to make it any clearer. This is my only gamble at what it is you might possibly want (not tested!):

update a
set a.RoleId = d.RoleId
FROM UserRoles a
inner join Users b on a.UserId = b.UserId
inner join Roles c on a.RoleId = c.RoleId
inner join Roles d
where c.RoleName= 'Administrator' AND b.UserName='someuser' AND d.RoleName='AnotherRole'

:)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Season's Greetings to all CPians.

modified on Friday, December 31, 2010 5:39 AM

[jboyd111]

Thanks for your help. I apologize I hadn't been able to properly characterize my problem. In short: I want to update a junction table (RoleId and UserId) when I only know the UserName and RoleName. I'll play around with variations of what you provided and search a bit further online. I'll post whatever resolution(s) I come up with.

[Jorgen Andersson]

I think you're thinking a bit wrong here (Alternatively have I understood you wrong). There's no real point in updating the UserRoles table. You want to add or remove roles a user have. There's nothing to update really. So you should rather think along the line of Insert and Delete instead. For example something similar to:

Insert Into UserRoles (UserID,RoleID)
Values ((Select Userid from Users Where UserName = 'someuser'),(Select RoleID from Roles Where Rolename = 'Administrator'))

Or:

Delete From UserRoles
Where Userid = (Select Userid from Users Where UserName = 'someuser')
And Roleid = (Select RoleID from Roles Where Rolename = 'Administrator')

"When did ignorance become a point of view" - Dilbert

[jboyd111]

OK, thank you, I'll think along the lines of DELETE and INSERT. The next step is to have this work with an SqlDataSource1_Updating method with my Gridview control in C#. Thanks to all for your help.