Update Oracle SQL Query throwing missing right parenthesis error

update mytable set node_index=0 where id in (
        SELECT 
            id
         FROM mytable
         WHERE 
            rownum<=10 and PROCS_DT is null  
         order by CRET_DT,PRTY desc) 

This is the error I am getting

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Ac开发者_开发知识库tion:

Whats wrong? Experts please help.. I am new to Oracle 11g

UPDATE  mytable
SET     node_index = 0
WHERE   rowid IN
        (
        SELECT  rid
        FROM    (
                SELECT  rowid AS rid
                FROM    mytable
                WHERE   procs_dt IS NOT NULL
                ORDER BY
                        cret_dt, prty
                )
        WHERE   rownum <= 10
        )

You can't have an order by clause in a subquery, if you think about it there is no point having one as you don't need to influence the order of an update. If you remove the order by your query should work and the outcome would be no different to if the order by were allowed.

I believe you need to remove the ORDER BY in the subquery. The order of the rows is meaningless to the logical result of the IN operation.

Generally, when Oracle gives this error but you have balanced parentheses, it means there is unexpected text in the parenthesized section.

Edit in response to comment

ROWNUM is calculated before the ORDER BY is applied. To do what you want, you need a nested subquery so the ordering will occur first.

update mytable set node_index=0 where id in (
    SELECT 
        id
     FROM
        ( SELECT id FROM mytable WHERE procs_dt IS NULL order by CRET_DT,PRTY desc)
     WHERE
        rownum<=10
    )

In this case, the ORDER BY is allowed because it does affect the outcome of the subquery.