views:

743

answers:

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

A: 

youTableAlias.Flags

In your example: g.Flags

Sergio
+9  A: 

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'
Jose Basilio
Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Group.Flags" could not be bound.
Alan
I get that error if I use Groups.Flags??
Alan
I changed the start of the UPDATE statement to simply "UPDATE g" instead of "UPDATE dbo.Groups". Please try that.
Jose Basilio
cool - wasn't aware you could use the alias, thanks
Chris Klepeis
+1  A: 

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'
Kevin
+1  A: 
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'
Tim Hoolihan
+1  A: 

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.

JohnFx
+1  A: 

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

Keith
+1  A: 
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).

David B
A: 

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')
Chris Klepeis
Ah, ive just realised, its because Im trying to set the flags to SET dbo.Group.Flags = dbo. Group.Flags | 512
Alan
ok, i just used select g.Flags from dbo.Groups g INNER JOIN dbo.UsersGroups ug ON g.GroupID = ug.GroupID INNER JOIN dbo.Users u ON u.UserID = ug.UserIDWHERE u.UserName = 'UserA' | 512in place of the dbo. Group.Flags | 512
Alan