0

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
Community
  • 1
  • 1
Nick
  • 37
  • 1
  • 1
  • 7
  • 1
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Feb 20 '15 at 16:58
  • 2
    Your joins are pointless, anyhow. All you need to do is `where S.ScoutID = @scoutID` on the first and `where SR.ScoutID = @ScoutID` on the second. \ –  Feb 20 '15 at 16:59
  • `SET` clauses can't use aliases. `SET ScoutRole.Role =...` and what @marc_s said, many times over. I know the other question is using this style, but copy-pasting queries without understanding what they're doing is not a good idea. – Jeroen Mostert Feb 20 '15 at 17:00
  • Is it best to just do two separate queries in that case? – Nick Feb 20 '15 at 17:02

1 Answers1

1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Christian Barron
  • 2,695
  • 1
  • 14
  • 22