UPDATE ORDER BY错误 [英] UPDATE ORDER BY error
问题描述
您好我正在尝试根据选择语句更新数据库，其中包含
ORDER BY。
由于我得到了错误，指出
服务器：消息1033，级别15，状态1，行13
ORDER BY子句在视图，内联函数中派生无效
表和子查询，除非还指定了TOP
这是我的sql语句：
SELECT SUBSTRING （A.DESCR，1,10），B.SUPPORT_TEAM_MBR，EMPLID，COUNT（*）
来自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）来自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
订购订购（A.DESCR，1,10）
我该怎么做才能避免这个问题。
预先感谢您的帮助。
如果您使用此查询，是否真的需要order by子句？作为一个
派生表？如果是这样，你可以使用''百分之百'来安抚编译器。
茶先生
" sqlgoogle" < RI ********* @ gmail.com>在消息中写道
news：11 ********************** @ o13g2000cwo.googlegr oups.com ...嗨我正在尝试根据其中包含
ORDER BY的select语句更新数据库。
由于我收到的错误表明
服务器：消息1033，级别15，状态1，行13
ORDER BY子句在视图，内联函数，派生的表和子查询中无效，除非还指定了TOP
这是我的sql声明：
来自PS_TEAM_CODE_TBL的SELECT SUBSTRING（A.DESCR，1,10），B.SUPPORT_TEAM_MBR，EMPLID，COUNT（*）
，PS_TEAM_MEMBERS B，PS_MEMBER_PERSON C
在哪里A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS =''A''
AND A.EFFDT> =（SELECT MAX（AX.EFFDT） ）来自PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD）
AND.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING（A .DESCR，1,10），B.SUPPO RT_TEAM_MBR，EMPLID
有COUNT（*）> 1
订购订购（A.DESCR，1,10）
我该怎么做才能避免这个问题。
先谢谢你的帮助。
您说您正在尝试更新，但您发布的是SELECT
语句。我假设SELECT语句在UPDATE语句中用作子查询
或派生表，或者用作视图。在每种情况下，ORDER BY
是不允许的。删除ORDER BY子句，它应该可以正常工作。你不能
订购更新。
-
David Portas
SQL Server MVP
-
David感谢您的回复。
我使用简单的更新&这里是完整的sql
UPDATE PS_MEMBER_PERSON
SET NAME1 =''Z''来自PS_MEMBER_PERSON，其中NAME1 =
（选择SUBSTRING（A.DESCR，1,10），B.SUPPORT_TEAM_MBR，EMPLID，COUNT（*）
来自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）来自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
有计数（*）> 1
订单BY SUBSTRING（A.DESCR，1,10））
如果我删除ORDER BY那么我会在子查询中出错。
什么你觉得我应该怎么解决这个问题。
提前感谢您的帮助
Hi I''m trying to update a db based on the select statement which has
ORDER BY in it.
And due to that I''m getting error which states that
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
Here is my sql statement:
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)
What should I do to avoid this problem.
Thanks in advance for your help.
解决方案Do you really need the order by clause if you are using this query as a
derived table? If so you can use ''top 100 percent'' to appease the compiler.
Mr Tea
"sqlgoogle" <ri*********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...Hi I''m trying to update a db based on the select statement which has
ORDER BY in it.
And due to that I''m getting error which states that
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
Here is my sql statement:
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)
What should I do to avoid this problem.
Thanks in advance for your help.
You say you are trying to do an UPDATE but what you posted was a SELECT
statement. I''m assuming that SELECT statement was used either as a subquery
or derived table in an UPDATE statement or as a view. In each case ORDER BY
isn''t allowed. Remove the ORDER BY clause and it should work fine. You can''t
have an ordered update.
--
David Portas
SQL Server MVP
--
David thanks for your response.
I using a simple update & here is the complete sql for that
UPDATE PS_MEMBER_PERSON
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))
If I remove the ORDER BY then I''ll get an error in subquery.
What do you think I should do to resolve that problem.
Thanks in advance for your help
这篇关于UPDATE ORDER BY错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
