LIMIT / OFFSET in Oracle 11G

Question

I am trying to update a table in Oracle and I ran into some difficulties. I am porting my code from MySQL and some of the commands that MySQL allows are not supported in Oracle.

Here is the MySQL code:

update table1 t1 set c5 = (select ContractID from table2 t2 where t1.assetid = t2.assetid and t1.lastdate >= t2.lastdate and t1.firstdate= t2.firstdate order by lastdate asc limit 1 offset 4); 

The subquery returns a list of ContractIDS sorted by lastdate, and I only need one, so the command limit 1 offset X.

The problem is as follows. Oracle does not support the "limit" or "offset" commands. There are workarounds to solve the problem of using rownum and nested queries, but the Oracle 11G parser is not like in the UPDATE command.

I had a similar problem before I needed a restriction in the update command, but not an offset. It was resolved here: MySQL Error for Oracle Syntax (constraint / offset / update)

There is a workaround that Florin Gita found in analytic functions.

  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 

This workaround allows me to get the top or bottom record (using asc or desc in the dense_rank command), but I cannot find the proxy for the offset command if I need a second or third line.

Another solution I tried was using a subquery. The former received the first 5 rows using the rownum command, ordering them in reverse order, MINUS-ed over the last four rows. This solution failed because the Oracle parser did not understand the table link in the outermost command referenced inside one of the nested queries.

(same problem as before: MySQL for Oracle syntax (constraint / offset / update) )

The task is not just to run the select statement in oracle with constraint and offset, since I can already do this with nested queries. The task is to make the select statement work in the update statement, because although the statement is syntactically correct, the Oracle analyzer cannot decode them. So far, nested queries (and Google) have not helped me.

Has anyone had similar problems?

Answer 1

Remote original answer, non-viable

I believe this should be done in a single SQL statement, but so far the combination of the need for a correlated subquery and the need for some kind of analytic function have done everything I tried.

Here's a procedural method that I think will do what you want:

 DECLARE CURSOR t IS SELECT LEAD(contractid,4) OVER (PARTITION BY assetid ORDER BY lasttradedate ASC) lead_contractid FROM table1 FOR UPDATE; BEGIN FOR r IN t LOOP UPDATE table1 SET nextcontractid = r.lead_contractid WHERE CURRENT OF t; END LOOP; END;