MySQL to Oracle Syntax Error (Limit / Offset / Update)

I have a MySQL query that works on my current MySQL database. I've been forced to move over to oracle, so I'm trying to port all my stored procedures / programs to use the Oracle SQL Syntax. I'm having a lot of trouble on one particular query. Here is the MySQL query. It updates a table using a subquery.

 update table1 alf 
 set nextcontractid = 
 (
 select 
    contractid from table1copy alf2 
 where 
    alf2.assetid = alf.AssetID 
 and 
    alf2.lasttradedate > alf.LastTradeDate 
 order by lasttradedate asc limit 1
 ) 
 where complete = 0

In oracle, I c开发者_C百科an't use the the limit command, so I've looked for the workaround. Here is my oracle query. (which doesn't work.)

 update table1 alf
 set nextcontractid = 
   (select contractid from
     (
     SELECT contractid, rownum as row_number
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     ORDER BY lasttradedate ASC
   ) 
   where row_number = 1)
 where alf.complete = 0

I get the following error:

 Error at Command Line:8 Column:29
 Error report:
 SQL Error: ORA-00904: "ALF"."LASTTRADEDATE": invalid identifier
 00904. 00000 -  "%s: invalid identifier"

line 8 is:

 AND alf2.lasttradedate > alf.lasttradedate

Removing the update statement and putting in some dummy values into the subquery yields the correct results for the subquery:

   (select contractid from 
     ( 
     SELECT contractid, rownum as row_number 
     FROM asset_list_futures_copy alf2 
     WHERE alf2.assetid     = 'GOLD' 
     AND alf2.lasttradedate > '20110101' 
     ORDER BY lasttradedate ASC 
     )  
    where row_number = 1)

Looking at the error, it looks like the second reference to alf isn't working. Any idea how I can change my query so that it works in oracle?

---

Seems the parser does not like that, despite the fact it is sintacticaly correct. Probably the two imbricated and ordered clause is blinding him somehow. I reproduced that.

You can use an analytical function:

update table1 alf
 set nextcontractid = 
     (SELECT min(contractid) keep (dense_rank first order by lasttradedate asc) 
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     )
 where alf.complete = 0

---

You can use WHERE rownum = 1, or rownum BETWEEN x AND y in cases that you want more results.