CODEWITHSUNDEEP
sqlsql-servert-sql
Alan
Alan

Reputation: 113

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.

Upvotes: 10

Views: 33972

Answers (9)

Shree Dhushandhan
Shree Dhushandhan

Reputation: 11

Mention the correct Table column for the ON clause to Condition

FROM database2.student student LEFT JOIN database2.college college ON student.college_id = college.college_id ORDER BY college.college_id

This Error occurs due to the Name Confusion between the Table Mention the Table column properly with the table Name This would work..

Upvotes: 0

kemiller2002
kemiller2002

Reputation: 115490

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'

Upvotes: 2

Amy B
Amy B

Reputation: 110101

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

Upvotes: 3

Chris Klepeis
Chris Klepeis

Reputation: 9983

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

Upvotes: 1

Jose Basilio
Jose Basilio

Reputation: 51468

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'

Upvotes: 18

Keith
Keith

Reputation: 739

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

Upvotes: 2

JohnFx
JohnFx

Reputation: 34909

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.

Upvotes: 5

Tim Hoolihan
Tim Hoolihan

Reputation: 12396

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'

Upvotes: 2

Sergio
Sergio

Reputation: 8259

youTableAlias.Flags

In your example: g.Flags

Upvotes: 1

Related Questions