StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate Junction table in many-to-many relationship
text
Body
copied!<p>This is a follow up question to this one:</p> <p><a href="https://stackoverflow.com/questions/416565/query-examples-in-a-many-to-many-relationship">Query examples in a many-to-many relationship</a></p> <p>regarding updating the junction table. To do this, I would have to use both key values in the junction table, in the WHERE clause.</p> <pre><code>Users UserAddresses Addresses ======= ============= ========= FirstName UserId City LastName AddressId State Zip </code></pre> <p>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.</p> <p>I'm using a stored procedure, and passing in UserId and the new AddressId as parameters.</p> <p>I've tries this:</p> <pre><code>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)?? </code></pre> <p>I've tried all sorts of combinations, but I can't seem to get the syntax right.</p> <p>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.</p> <p>Any thoughts?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload