CODEWITHSUNDEEP
sqlsql-server
Nick
Nick

Reputation: 37

Updating two tables in one SQL query

I'm attempting to update my Scout and ScoutRole tables from one SQL query. I've tried to follow this example: How to update two tables in one statement in SQL Server 2005?.

But I keep receiving the error message

The multi-part identifier "SR.Role" could not be bound

How do I resolve this? 

BEGIN TRANSACTION

UPDATE Scout
SET Scout.FirstName = @ScoutFirstName, 
    Scout.LastName = @ScoutLastName, 
    Scout.EmailAddress = @EmailAddress,
    Scout.ClubID = @ClubID
FROM Scout S, ScoutRole SR
WHERE S.ScoutID = SR.ScoutID AND S.ScoutID = @ScoutID

UPDATE ScoutRole
SET SR.Role = @ScoutRole,
    SR.Username = @Username,
    SR.Password = @Password
FROM Scout S, ScoutRole SR
WHERE S.ScoutID = SR.ScoutID AND S.ScoutID = @ScoutID

COMMIT

Upvotes: 0

Views: 85

Answers (1)

Christian Barron
Christian Barron

Reputation: 2755

This should be all you need to use:

BEGIN TRANSACTION

UPDATE Scout
SET FirstName = @ScoutFirstName, 
    LastName = @ScoutLastName, 
    EmailAddress = @EmailAddress,
    ClubID = @ClubID
WHERE ScoutID = @ScoutID

UPDATE ScoutRole
SET Role = @ScoutRole,
    Username = @Username,
    Password = @Password
WHERE ScoutID = @ScoutID

COMMIT

Upvotes: 1

Related Questions