How to convert this MsSQL statement into Oracle?

[Michael Long]

I have a SQL statement that performs correctly using MS Sql (all versions), but as I'm attempting to convert it Oracle 11g, I'm having some issue where the return result says that the command is not properly ended. Yet, the subquery seems to operate correctly if I remove the update parameters.

What this sql is supposed to do is select the first 50 rows, then update each of the selected rows with specific values.

Original MS Sql version:
Update CRPDTAMX1.F55UK RQ Set CRPDTAMX1.F55UK RQ.KQZKV8='PMX' , CRPDTAMX1.F55UK RQ.KQZKV9 ='PMX', CRPDTAMX1.F55UK RQ.KQZKSTS ='Submit', CRPDTAMX1.F55UK RQ.KQZKCDE ='1' FROM (Select TOP 50 * From CRPDTAMX1.F55UK RQ Where (((CRPDTAMX1.F5 5UKRQ.KQZKV9 ='' or CRPDTAMX1.F55UK RQ.KQZKV9 ='0' Or CRPDTAMX1.F55UK RQ.KQZKV9 Is Null) Or (CRPDTAMX1.F55U KRQ.KQZKV8 ='' And CRPDTAMX1.F55UK RQ.KQZKV9 ='PMX') Or (CRPDTAMX1.F55U KRQ.KQZKV8 ='PMX' And CRPDTAMX1.F55UK RQ.KQZKV9 ='PMX')) And (CRPDTAMX1.F55U KRQ.KQZKSTS ='Ready' Or CRPDTAMX1.F55UK RQ.KQZKSTS ='Active')) And KQACTI <> 'R' Order by CRPDTAMX1.F55UK RQ.KQZKCDE, CRPDTAMX1.F55UK RQ.KQZKID) AS KQ Where CRPDTAMX1.F55UK RQ.KQZKID= KQ.KQZKID

Oracle converted version:
UPDATE CRPDTAMX1.F55UK RQ A
Set A.KQZKV8='PMX', A.KQZKV9 ='PMX', A.KQZKSTS ='Submit', A.KQZKCDE ='1'
From (Select *
From CRPDTAMX1.F55UK RQ C
Where (((C.KQZKV9 ='' or C.KQZKV9 ='0' Or C.KQZKV9 Is Null) Or (C.KQZKV8 ='' And C.KQZKV9 ='PMX') Or (C.KQZKV8 ='PMX' And C.KQZKV9 ='PMX')) And (C.KQZKSTS ='Ready' Or C.KQZKSTS ='Active')) And KQACTI <> 'R' and ROWNUM <=50
Order by C.KQZKCDE, C.KQZKID) B
Where A.KQZKID=B.KQZK ID;

What I'm getting from the Oracle db is the following:
[Oracle][ODBC][Ora]ORA-00933 Sql command not properly ended

Any help would be greatly appreciated! Thanks in advance for your help!

[amitpatel66]

Its becuase of incorrect syntax. You need to use something like this:

[code=oracle]
UPDATE CRPDTAMX1.F55UK RQ A
Set A.KQZKV8='PMX', A.KQZKV9 ='PMX', A.KQZKSTS ='Submit', A.KQZKCDE ='1'
Where EXISTS (SELECT 1 FROM CRPDTAMX1.F55UK RQ C
WHERE A.KQZKID=B.KQZK ID)
/
[/code]

You can add all your conditions for table CRPDTAMX1.F55UK RQ in the above query