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.F55UKRQ Set CRPDTAMX1.F55UKRQ.KQZKV8='PMX', CRPDTAMX1.F55UKRQ.KQZKV9 ='PMX', CRPDTAMX1.F55UKRQ.KQZKSTS ='Submit', CRPDTAMX1.F55UKRQ.KQZKCDE ='1' FROM (Select TOP 50 * From CRPDTAMX1.F55UKRQ Where (((CRPDTAMX1.F55UKRQ.KQZKV9 ='' or CRPDTAMX1.F55UKRQ.KQZKV9 ='0' Or CRPDTAMX1.F55UKRQ.KQZKV9 Is Null) Or (CRPDTAMX1.F55UKRQ.KQZKV8 ='' And CRPDTAMX1.F55UKRQ.KQZKV9 ='PMX') Or (CRPDTAMX1.F55UKRQ.KQZKV8 ='PMX' And CRPDTAMX1.F55UKRQ.KQZKV9 ='PMX')) And (CRPDTAMX1.F55UKRQ.KQZKSTS ='Ready' Or CRPDTAMX1.F55UKRQ.KQZKSTS ='Active')) And KQACTI <> 'R' Order by CRPDTAMX1.F55UKRQ.KQZKCDE, CRPDTAMX1.F55UKRQ.KQZKID) AS KQ Where CRPDTAMX1.F55UKRQ.KQZKID= KQ.KQZKID

Oracle converted version:
UPDATE CRPDTAMX1.F55UKRQ A
Set A.KQZKV8='PMX', A.KQZKV9 ='PMX', A.KQZKSTS ='Submit', A.KQZKCDE ='1'
From (Select *
From CRPDTAMX1.F55UKRQ 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.KQZKID;

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:

UPDATE CRPDTAMX1.F55UKRQ A
Set A.KQZKV8='PMX', A.KQZKV9 ='PMX', A.KQZKSTS ='Submit', A.KQZKCDE ='1'
Where EXISTS (SELECT 1 FROM CRPDTAMX1.F55UKRQ C 
 WHERE A.KQZKID=B.KQZKID)
/
 

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