UPDATE ORDER BY error

[sqlgoogle]

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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TB L A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSO N 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_TB L 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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DES CR,1,10)

What should I do to avoid this problem.

Thanks in advance for your help.

[Lee Tudor]

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*********@gm ail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TB L A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSO N 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_TB L 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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DES CR,1,10)

What should I do to avoid this problem.

Thanks in advance for your help.

[David Portas]

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

[sqlgoogle]

David thanks for your response.
I using a simple update & here is the complete sql for that

UPDATE PS_MEMBER_PERSO N
SET NAME1 = 'Z' FROM PS_MEMBER_PERSO N WHERE NAME1 =
(SELECT SUBSTRING(A.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TB L A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSO N 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_TB L 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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DES CR,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

[sqlgoogle]

Thanks Mr Tea for your response.

I have never used the top percent before. I couldn't find any solid
example that should explain how this works.
I guess I could try it if I know how to do it.
Do you have or know a sample or example that explain this in details.

Thanks

[David Portas]

Your query is supposed to be a scalar subquery but it is returning more than
one column. That can't be. Also GROUP BY is pointless in a scalar subquery
because only one row must be returned. Finally, your subquery isn't
correlated with the UPDATE.

To help you properly I think we'll need more information. If you need more
help maybe you could post DDL (just the essential columns and the keys will
do), sample data INSERTs and show your required end result. See the
following article on how to do this:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

[Lee Tudor]

You can find an example in the books online although in this case it
probably means you are trying to do something maybe incorrect for what you
envisaged.

you could turn your select into a single column select and use UPDATE ...
WHERE x IN (subquery), dropping the order by clause from the subquery.
although this is just a guess as to what you are trying to acheive and you
should post the DDL as requested if you need a better review.

Regards
Mr Tea

"sqlgoogle" <ri*********@gm ail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Thanks Mr Tea for your response.

I have never used the top percent before. I couldn't find any solid
example that should explain how this works.
I guess I could try it if I know how to do it.
Do you have or know a sample or example that explain this in details.

Thanks

[Erland Sommarskog]

sqlgoogle (ri*********@gm ail.com) writes:

David thanks for your response.
I using a simple update & here is the complete sql for that

UPDATE PS_MEMBER_PERSO N
SET NAME1 = 'Z' FROM PS_MEMBER_PERSO N WHERE NAME1 =
(SELECT SUBSTRING(A.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TB L A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSO N 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_TB L 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.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DES CR,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.

So you got an error in the subquery, and then you thought "Ah, if
I add an ORDER BY to the subquery that error will go away!"?

Excuse me the sarcasm, but I have to confess that I can't really
understand how you could the idea that the above could work at all.
Exactly what is

NAME1 =
(SELECT SUBSTRING(A.DES CR,1,10), B.SUPPORT_TEAM_ MBR, EMPLID, COUNT(*)

intended to mean? You can't compare NAME1 to all four values at a
time anyway.

It seems that you are into some handling of duplicates, but I don't
feel like going into wild guesses of what you are doing. So I suggest
that you rewind, and gives us more information of the problem you
are trying to solve. The standard recommendation is to include:

o CREATE TABLE statements of the involved tables. Don't forget to
include information about keys.
o INSERT statements with sample data.
o The desired result given the sample.

Apparently quite a few tables are involved in your case, so it might be
a good idea to simplify the problem somewhat.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[sqlgoogle]

Hi David thanks for your tips.
I don't have access to Ent. Mgr (due to rights) but I hope this will
help

Name Owner
Type

-----------------------------------------------------------------------------------------------------------------
------------------------------- --------------------------
PS_TEAM_CODE_TB L dbo user
table

Column_name Type
FixedLenNullInS ource

--------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
SETID char
no
SUPPORT_TEAM_CD char no
EFFDT PSDATE
(n/a)
EFF_STATUS char
no
DESCR char
no
DESCRSHORT char
no
Identity Seed
Increment Not For Replication
--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------- -- No identity column defined.
NULL NULL
NULL
RowGuidCol

--------------------------------------------------------------------------------------------------------------------------------

No rowguidcol column defined.
Data_located_on _filegroup

--------------------------------------------------------------------------------------------------------------------------------

PRIMARY

Index_keys
DESCR, SETID, SUPPORT_TEAM_CD , EFFDT
SETID, SUPPORT_TEAM_CD , EFFDT
No constraints have been defined for this object.

No foreign keys reference this table.
No views with schema binding reference this table.
Name Owner
Created_datetim e
--------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
PS_TEAM_MEMBERS dbo
user table

Column_name Type Length
FixedLenNullInS ource
--------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
SETID char 5 no
SUPPORT_TEAM_CD char 6 no
EFFDT PSDATE 8 no
SUPPORT_TEAM_MB R char 8 no
MANAGER char 1 no

PRIORITY smallint 2 (n/a)
Identity Seed
Increment Not For
Replication
--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------- No identity column defined.
NULL NULL
NULL
RowGuidCol

--------------------------------------------------------------------------------------------------------------------------------

No rowguidcol column defined.
Data_located_on _filegroup

--------------------------------------------------------------------------------------------------------------------------------

PRIMARY
index_keys
PS_TEAM_MEMBERS

Name Owner Type

--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------
PS_MEMBER_PERSO N dbo user table

Column_name Type Length
FixedLenNullInS ource
--------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
SETID char
5 no
SUPPORT_TEAM_MB R char 8
no
EMPLID char 11
no
NAME1 char 40
no
TEAM_MEMBER_TYP E char 6
no
EFF_STATUS char 1
no
VENDOR_ID char 10
no
REGION_CD char 10
no
LANGUAGE_CD char 3
no
DEF_COMPCT_SRC char 4
no
QUOTA_TYPE char 1
no
COMM_PCT decimal 5
(n/a)
COMM_BKS decimal 5
(n/a)
COMM_SHP decimal 5
(n/a)
COMM_INV decimal 5
(n/a)
COMM_CASH decimal 5
(n/a)
EXT_EMAIL_ADDR char 70
no
CURRENCY_CD char 3
no
RowGuidCol

--------------------------------------------------------------------------------------------------------------------------------

No rowguidcol column defined.
Data_located_on _filegroup

--------------------------------------------------------------------------------------------------------------------------------

PRIMARY
I'm trying to put the sample data together & I'll post that later.

Thanks

[Erland Sommarskog]

sqlgoogle (ri*********@gm ail.com) writes:

Thanks for the tip Erland. I've tried that already & I'm getting login
error.
Which I'm guessing due to my access rights.

Any other way to get around or any other tip.

OK, it's only to start typing then!
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp