missing right parenthesis with update statment

Question

i am trying to make a normal update statement .

update adm_types_uda set type_id =28202 where recid in (
SELECT  recid FROM 
    (
    SELECT *FROM adm_types_uda 
    ORDER BY recid desc
    )
WHERE rownum <= 284
ORDER BY recid )

it gives me

SQL Error: ORA-00907: missing right parenthesis

the inner select between in worked well independent .

i try to give all select an alias but still give the same error

update adm_types_uda uda set uda.type_id =28202 where uda.recid in (
SELECT  gd.recid FROM 
    (
    SELECT * FROM adm_types_uda u
    ORDER BY u.recid desc
    ) gd
WHERE rownum <= 284
ORDER BY gd.recid)

can any one help ?

William Robertson · Accepted Answer · 2018-04-18T10:38:34.447

2

Fixed version:

update demo
set    type_id = 28202
where  recid in
       ( select recid from 
                ( select *from demo 
                  order by recid desc )
         where rownum <= 284 )

My first attempt which was wrong (thanks mathguy) was to refactor the outer where rownum ... order by ... by moving it into the inner subquery, however I missed that the outer order by was redundant anyway and it just needed removing.

The 'missing right parenthesis' error was because the parser doesn't recognise the order by in the subquery as this is not allowed (I couldn't see any explicit statement about this in the SQL reference, but it isn't).

edited Apr 18 '18 at 10:38

answered Apr 17 '18 at 17:13

William Robertson

15,273
4
38
44

1

This is incorrect (or at least not equivalent to the OP's attempt); if you select ROWNUM in the same query where you ORDER, the result is NOT a "top-n" output. – Apr 17 '18 at 17:22
selecting rownum as rn returns wrong data – Yousef Al Kahky Apr 17 '18 at 17:24
1

You are quite right, thanks. Fixing. – William Robertson Apr 17 '18 at 17:26
thanks , worked with me – Yousef Al Kahky Apr 18 '18 at 09:49

score 2 · Answer 2 · answered Apr 17 '18 at 17:21

2

Remove the last ORDER BY clause, which doesn't do anything anyway (it makes no sense to ORDER a set of rows used in an IN condition) and is illegal there.

After you delete it, the query will work just fine.

answered Apr 17 '18 at 17:21

no i need it to return the last 284 row only – Yousef Al Kahky Apr 17 '18 at 17:36
2

@YousefAlKahky - That is the query does. The ORDER BY in the innermost query is the one that takes care of "last". In the outer query, BASED ON THIS ORDER from the INNERMOST query, the ROWNUM condition limits the selected rows to the last 284. Try it with a smaller number (perhaps 10) instead of 284, to convince yourself that this is true. You don't need the SECOND ORDER BY. – Apr 17 '18 at 17:44
you are right , thank you :) – Yousef Al Kahky Apr 18 '18 at 09:48

missing right parenthesis with update statment

2 Answers2