0
UPDATE CONVERTED T1 
   SET PARENTID = (SELECT ID FROM 
                  CONVERTED T2 WHERE T2.ID < T1.ID 
                  AND T1.PREVOBJNUM = T2.OBID 
                  AND ROWNUM = 1 
                  ORDER BY ID DESC)

Gives me the following error

L Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

If I remove the ORDER BY it works. Any idea how to fix it?

totalitarian
  • 3,606
  • 6
  • 32
  • 55

1 Answers1

2

You can't have an orderby in that statement. Modify it like so to get around the restriction. Also, if you want to return the correct record, I would suggest moving the rownum outside to the second select. Rownum will be incorrect in the inner select since it's applied before the orderby. If you filter by the rownum in the inner select, it's hit or miss whether or not you'll select the max ID, which is what it looks like you want.

UPDATE CONVERTED T1 
   SET PARENTID = (
    SELECT ID FROM (
        SELECT ID FROM 
        CONVERTED T2 WHERE T2.ID < T1.ID 
        AND T1.PREVOBJNUM = T2.OBID 
        ORDER BY ID DESC)
    WHERE ROWNUM = 1 
    )
Joel
  • 2,227
  • 1
  • 17
  • 23
  • This raises the exception `ORA-00904: "T1"."PREVOBJNUM": invalid identifier` [SQLFIDDLE](http://sqlfiddle.com/#!4/9aec14/1) – MT0 Dec 06 '18 at 09:09