Ambiguous column name error, how do I fix it?

sql, sql-server, t-sql

Solution

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'

Problem

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

Original source