Each GROUP BY expression must contain at least one column that is not an outer reference
-
hi guys,
We have just replaced SQL 2000 with SQL 2005.
The following type of statements are creating a problem in 2005 while
they wer running fine in 2000.
Consider the update statement:
update a
set a.col3 = (select count(b.idcol)
from tableB b with (nolock)
where b.idcol = a.idcol
group by a.col2
 
from tableA a with (nolock)
As you see, the group by clause in the select query references only the
column of the outer table tableA.
This throws an error in SQL 2005 (not in SQL 2000) saying :
"Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not
an outer reference."
Could not find any documentation about this error .
In the above case, i gather, my group by clause should also contain at
least one column of the inner table tableB??
How do i get rid of this error without losing my functionality and
minimal code change, as of now!!!!
-
I don't understand why you have a group by clause in the subquery. It will fail if it returns more than one row anyway, bacause you can't assign more than one value to a single column in a single row.
It should work OK if you just remove the "group by a.col2".
I doubt that the NOLOCK hints are a good idea either. The NOLOCK on tableA will be ignored anyway, because it has to get locks to update the table. Since you could be doing an update based on uncommitted data in tableB, that looks bad too.
-
Answered here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=338509
Please don't cross-post, we do see all new posts to all forums.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply