Ambiguous column name error, how do I fix it?

问题:

1. Users 4 Cols
UserID - UserName - RealName - Flags

2. UsersGroups 2 Cols
UserID - GroupID

3. Groups 3 Cols
GroupID - GroupName - Flags

What I want to do is select a specific UserName ie USERA and update the Flags column. but I also want to update the Flags column in the Groups table to the same value.

UPDATE dbo.Users
SET Flags = @var
WHERE UserName = 'UserA'

UPDATE dbo.Groups
SET Flags = @var
FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
WHERE u.UserName = 'UserA'

but I keep getting : Ambiguous column name 'Flags'.

if I do Set Groups.Flags = @Var i get : Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Groupy.Flags" could not be bound.

回答1:

You need to add the alias for the Groups table. Change this:

UPDATE dbo.Groups
SET Flags = @var
FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
WHERE u.UserName = 'UserA'

To this:

UPDATE g -- change dbo.Groups here to simply 'g'
SET g.Flags = @var
FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
WHERE u.UserName = 'UserA'

回答2:

The problem is that you haven't specified the table name for the field "Flags" and it probably exists in more than one table in the query. Add the table name in the format "Tablename.flags" to the front of all references to fix the problem.

回答3:

UPDATE g
SET g.Flags = @var
FROM
  dbo.Groups g
    INNER JOIN
  dbo.UsersGroups ug
    ON g.GroupID = ug.GroupID
    INNER JOIN
  dbo.Users u
    ON u.UserID = ug.UserID
WHERE u.UserName = 'UserA'

• In the from clause - the update target needs to be the first table there.
• In the update clause - use the table alias created in the from clause.
• In the set clause - use the table alias created in the from clause.

I once knew the reasons that this dance needs to be done this way - now I just do it out of habit. I suspect it has something to do with TSQL's double FROM clause in DELETE statements, and the possibility of talking about Two different instances of the Groups table between the FROM and UPDATE clause... or even Two different instances of the Groups table in the from clause (think self-join).

回答4:

UPDATE dbo.Groups Set dbo.Groups.Flags = @var FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID WHERE u.UserName = 'UserA'

回答5:

Try SET Groups.Flags = @var in your second update

回答6:

Just do alias.Flags or TableName.Flags in the update statement.

So it becomes this:

UPDATE dbo.Users
     SET Flags = @var
     WHERE UserName = 'UserA'

UPDATE g
   SET g.Flags = @var
FROM dbo.Users u 
INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
INNER JOIN dbo.Groups g       ON g.GroupID = ug.GroupID
WHERE u.UserName = 'UserA'

回答7:

youTableAlias.Flags

In your example: g.Flags

回答8:

Here's a workaround (albeit maybe not the best solution):

UPDATE dbo.Groups
SET Flags = @var
FROM dbo.UsersGroups ug INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
WHERE ug.UserID IN (SELECT UserID FROM dbo.Users WHERE UserName = 'UserA')