Update Junction table in many-to-many relationship

This is a follow up question to this one:

http://stackoverflow.com/questions/416565/query-examples-in-a-many-to-many-relationship

regarding updating the junction table. To do this, I would have to use both key values in the junction table, in the WHERE clause.

Users       UserAddresses     Addresses
=======     =============     =========
FirstName   UserId            City
LastName    AddressId         State
                              Zip

In this example, for instance, say I wanted to update the AddressId field in the UserAddresses table, because a user changed his address. I would have to use both the existing UserId and the address AddressId in the update WHERE clause.

I'm using a stored procedure, and passing in UserId and the new AddressId as parameters.

I've tries this:

CREATE PROCEDURE dbo.test
(
@UserId int,
@AddressId int
)
AS
create table #temp
    (
    UserId int not null,
    AddressId int not null
    )
    insert into #temp select UserId, AddressId from UserAddresses where UserId = @UserId

update UserAddresses
set AddressId = @AddressIdD
WHERE (UserId+AddressId in #temp table = UserId+AddressId passed in as parameters)??

I've tried all sorts of combinations, but I can't seem to get the syntax right.

The UserId passed in, would ofcourse be the same as the one in the UserAddresses table, but this is just me trying some things. The WHERE clause is where it seems to go wrong.

Any thoughts?

From stackoverflow

• This actually looks like a many-to-one relationship. If it's not you'll need the old address id as well as the new address id and user id to make the change. If it's a many to one relationship then a simple update should work since only one user id/address id pair will exist for each user id:

  update UserAddresses
  set AddressId = @AddressId
  where UserId = @UserId
  

  If it truly is a many-to-many relationship you need to find the existing pair out of many possible ones and update that one -- that's where you'll need both the new and old address ids in addition to the user id.

  update UserAddresses
  set AddressId = @NewAddressId
  where UserId = @UserId and @AddressId = @OldAddressId
  

  Soeren : It is a M-T-M rel., and that's why I wanted to select the existing UserId and AddressId into a temp table, and then refer to that table in the actual update. I think this should be possible, but I can't get the syntax right.

  tvanfosson : Yes, but you are only selecting by UserID into the temp table. If a user can have many addresses, then you need to find the pair matching the old address that is being removed and replace just it.

• Why use the temp table?

  CREATE PROCEDURE dbo.test
  (
  @UserId int,
  @AddressId int
  )
  AS
  update UserAddresses
  set AddressId = @AddressIdD
  WHERE UserId = @UserId
  

• tvanfossom pointed out the problem in your code correctly, I think.

  With the tables above, your operation could be done in various ways:

  • INSERT the link to the new address and DELETE the link to the old address, either keeping or deleting the address record that's being linked to.
  • UPDATE the link record as tvanfossom described (LuckyLindys query will set all registered addresses of the user to the same one).
  • UPDATE the address record that's being linked to.

  Which one you use depends on what you want in your application. I'd probably just update the linked address, or do you need to keep the old one?