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

Oracle parameterized queries to update Oracle table do not work

Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.

Anyone knows how to update an Oracle table through SSIS?

Thanks!

Wenbiao

Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.|||

Phil Brammer wrote:

Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.

Thanks Phil,

Your post is really helpful, however, I have a couple of more questions.

1. You said load the updates to a table, this has to be a temp table I guess?

2. Could you please write in more details on how to "perform a batch update" in an Execute SQL task, such as what the SQL statement will look like? Do I still need to provide parameters, such as "update dest_table set xxx=? where yyy=?"

Thanks a lot!

Wenbiao

|||Yes, it is a temp table. But not a #temp table. It's just a physical table that you store the updates in. When you're done, you can issue an Execute SQL task to truncate that table.

As far as the batch update goes: (Something like this, I believe)

UPDATE myTable a INNER JOIN stagingTable b ON a.key = b.key
SET a.field1 = b.field1, a.field2 = b.field2|||

here is an example of the update syntax:

UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

|||Thanks Phil and Rafael, I will give it a try and report back.

Oracle DB Update Error With SQL

I keep getting this error when i try to update to an oracle table through SQL:

An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].

This is the code I'm using:

**************

DECLARE @.cmd VARCHAR(500)

DECLARE @.Link VARCHAR(100)
set @.Link = Oracle_DB'

DECLARE @.Table VARCHAR(100)
set @.Table = 'Product_table'
declare @.Factory varchar(3)
set @.Factory = 'N12'

declare @.ProdPull varchar(10)
set @.ProdPull = '10500'

declare @.Prod varchar(25)
set @.Prod = ' 44JBGG'

declare @.WW varchar(6)
set @.WW = '200704'

declare @.SchedType varchar(15)
set @.SchedType = 'SCHED 1'

SET @.cmd =
'
select * from OPENQUERY
(
' + @.Link + ',
''
UPDATE '+ @.Table +'
SET TARGET_VALUE = '+@.ProdPull+'
WHERE TARGET_LEVEL2_VALUE = '+@.Prod+'
AND PERIOD_VALUE = '+@.WW+'
AND TARGET_LEVEL_VALUE = '+@.Factory+'
AND TARGET_TYPE = '+@.SchedType+'
''
)
'

EXEC (@.cmd)

*********

Have no idea why this isn't working .. help appreciated.

You need quotes around your string values in the update.

UPDATE '+ @.Table +' SET TARGET_VALUE = '''+@.ProdPull+''' WHERE TARGET_LEVEL2_VALUE = '''+@.Prod+''' AND PERIOD_VALUE = '''+@.WW+''' AND TARGET_LEVEL_VALUE = '''+@.Factory+''' AND TARGET_TYPE = '''+@.SchedType+''''
|||

spot on that worked 100%

but now I am getting this error Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.

I tried turning XACT_ABORT on but then the update statement just doens't work.

Any ideas anybody.

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DTB" <macdtb@.mac.com> wrote in message
news:4af8df45.0308170552.27c24433@.posting.google.c om...
> I am having trouble creating an INSTEAD OF trigger in SQL Server to
> replicate a BEFORE UPDATE trigger from ORACLE.
> Here is a sample of the ORACLE BEFORE UPDATE trigger:
> CREATE TRIGGER myTRIGGER ON MYTABLE
> begin
> :new.DT := SYSDATE;
> if :new.NM is NULL then
> :new.NM := USER;
> end if;
> end myTRIGGER;
> It seems as though I have to jump through hoops in SQL Server AND I
> cannot come up with correct results.
> Here is a snippet from SQL SERVER (this is what I figured I needed to
> do after reading various articles,questions):
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> SELECT * INTO #MYTABLE FROM INSERTED
> UPDATE #MYTABLE SET DT = GETDATE()
> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> UPDATE THETABLE
> SET
> DT = (SELECT DT FROM #MYTABLE),
> NM = (SELECT NM FROM #MYTABLE)
> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.|||Thank you Dan! This works the way I want it to.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<ZfN%a.27352$vo2.5843@.newsread1.news.atl.earthlink. net>...
> You don't need to use temp tables and subqueries here. The example
> below will do the job, assuming that ID is the primary key and is never
> changed. Note that you'll also need to include other columns in the
> UPDATE statement in order for those to be updated.
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> UPDATE THETABLE
> SET
> DT = GETDATE(),
> NM = COALESCE(inserted.NM, USER)
> FROM inserted
> WHERE THETABLE.ID = inserted.ID
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --------
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
> "DTB" <macdtb@.mac.com> wrote in message
> news:4af8df45.0308170552.27c24433@.posting.google.c om...
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> > Can anyone please shed some light on this? Thanks in advance.|||I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind

I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.

Originally posted by Dtb

> I am having trouble creating an INSTEAD OF trigger in SQL Server to

> replicate a BEFORE UPDATE trigger from ORACLE.

> Here is a sample of the ORACLE BEFORE UPDATE trigger:

> CREATE TRIGGER myTRIGGER ON MYTABLE

> begin

> :new.DT := SYSDATE;

> if :new.NM is NULL then

> :new.NM := USER;

> end if;

> end myTRIGGER;

> It seems as though I have to jump through hoops in SQL Server AND I

> cannot come up with correct results.

> Here is a snippet from SQL SERVER (this is what I figured I needed to

> do after reading various articles,questions):

> CREATE TRIGGER myTRIGGER on THETABLE

> INSTEAD OF UPDATE

> AS

> SELECT * INTO #MYTABLE FROM INSERTED

> UPDATE #MYTABLE SET DT = GETDATE()

> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

> UPDATE THETABLE

> SET

> DT = (SELECT DT FROM #MYTABLE),

> NM = (SELECT NM FROM #MYTABLE)

> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com|||Milind,

you are correct that the current problem is, that your trigger acts on
all rows of the table instead of just the inserted.

In the trigger context, all inserted rows are available in the virtual
table "inserted". (Lookup "triggers, inserted tables" in BOL for more
information).

Assuming "id" is the primary key of your table, you could use:

CREATE TRIGGER TR_TFACP200
ON TFACP200
FOR INSERT
AS

UPDATE TFACP200
SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)
WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1
AND DOCUMENT_DATE IS NULL
AND EXISTS (
SELECT 1
FROM inserted
WHERE inserted.id = TFACP200.id
)

Hope this helps,
Gert-Jan

Milind wrote:
> I'm tring to import the data from text file into Table and table have
> the trigger. but it takes too long time
> here is code
> CREATE TRIGGER TR_TFACP200
> ON TFACP200
> FOR INSERT
> AS
> UPDATE TFACP200
> SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)
> WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)) = 1
> AND DOCUMENT_DATE IS NULL
> can any give me the solution, i thing this is happening boz. UPDATE
> statement is fire on all records when we insert a single record.
> I think if i use BEFORE INSERT trigger like ORACLE then it solve the
> problem, but how i use the BEFORE INSERT trigger in SQL server
> I'm using SQL Server 7.0, and INSTEAD OF option not available.
> pls help me.
> Milind
> I am having trouble creating an INSTEAD OF trigger in SQL Server version
> 7.0 to replicate a BEFORE INSERT trigger from ORACLE.
> Originally posted by Dtb
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.
> --
> Posted via http://dbforums.com