SQL 不明确的列名错误，我该如何解决？

Ambiguous column name error, how do I fix it?

提问by Alan

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 UserNameie USERA and update the Flags column. but I also want to update the Flags column in the Groups table to the same value.

我想要做的是选择一个特定的用户名，即 USERA 并更新标志列。但我也想将 Groups 表中的 Flags 列更新为相同的值。

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.

如果我设置 Groups.Flags = @Var 我得到：Msg 4104, Level 16, State 1, Line 1 无法绑定多部分标识符“Groupy.Flags”。

回答by Jose Basilio

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

您需要为 Groups 表添加别名。改变这个：

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'

回答by JohnFx

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.

问题是您没有为字段“Flags”指定表名，它可能存在于查询中的多个表中。将格式为“Tablename.flags”的表名添加到所有引用的前面以解决问题。

回答by Amy B

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.

• 在 from 子句中 - 更新目标需要是那里的第一个表。
• 在更新子句中 - 使用在 from 子句中创建的表别名。
• 在 set 子句中 - 使用在 from 子句中创建的表别名。

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).

我曾经知道这种舞蹈需要以这种方式完成的原因——现在我只是出于习惯而这样做。我怀疑这与 DELETE 语句中 TSQL 的双 FROM 子句有关，以及在 FROM 和 UPDATE 子句之间谈论 Groups 表的两个不同实例的可能性......甚至在 from 中 Groups 表的两个不同实例条款（想想自加入）。

回答by Keith

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

在第二次更新中尝试 SET Groups.Flags = @var

回答by kemiller2002

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

只需在更新语句中执行 alias.Flags 或 TableName.Flags 即可。

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'

回答by Tim Hoolihan

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'

回答by Sergio

youTableAlias.Flags

youTableAlias.Flags

In your example: g.Flags

在您的示例中：g.Flags

回答by Chris Klepeis

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')