Order By Update Error

Hi Every1,
I'm getting the following error
Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.
what I'm trying to do is to update a table based on the select criteria
I have. In that I'm using ORDER BY Clause & this is giving me error.
Here is my sql:
SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Is it possible to fix this error?
Thanks in advance for your help.Tony,
The query you posted is not an UPDATE statement, View or Function.
I don't see any problems with the query you posted. However, it is not
valid as a view definition, because of the ORDER BY clause, missing
column names for the resultset and maybe more.
In what way to you think that the order is important when updating a
table? What is the actual UPDATE statement you are trying to use?
Gert-Jan
Tony Schplik wrote:
> Hi Every1,
> I'm getting the following error
> Server: Msg 1033, Level 15, State 1, Line 13
> The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.
> what I'm trying to do is to update a table based on the select criteria
> I have. In that I'm using ORDER BY Clause & this is giving me error.
> Here is my sql:
> SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
> from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
> WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
> AND A.EFF_STATUS = 'A'
> AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
> WHERE A.SETID = AX.SETID
> AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
> AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
> GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
> HAVING COUNT(*) > 1
> ORDER BY SUBSTRING(A.DESCR,1,10)
> Is it possible to fix this error?
> Thanks in advance for your help.|||Gert-Jan,
Thanks for your response. Sorry I forgot to put the update statement.
Here it is
UPDATE Name1
SET NAME1 = 'Z' FROM PS_MEMBER_PERSON WHERE NAME1 =(SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Even if I take out the order by clause in the update, then it will give
me a different error for the subquery
Do you have any suggestions for that ...
Thanks in advance for your help|||Tony,
Have you looked into the syntax of the UPDATE statement in SQL Server Books
Online?
--
Anithsql