7

I'm trying to update a table in Oracle and I'm running into some difficulty. I'm porting over 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 want a particular one, hence the limit 1 offset X command.

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

I had a similar problem before where I needed a limit within an update command, but not an offset. It was solved here: MySQL to Oracle Syntax Error (Limit / Offset / Update)

There is a workaround that Florin Ghita found using analytical 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 entry (by using asc or desc in the dense_rank command), but I am not able to find a proxy for the offset command, if I wanted the second or third row.

Another solution I've tried used a nested query. The first got the first 5 rows using the rownum command, ordered them in the opposite way, the MINUS-ed off the last four rows. This solution failed because the Oracle parser didn't understand the reference to a table in the outermost command referenced inside one of the nested queries.

(Same problem as I was having before: MySQL to Oracle Syntax Error (Limit / Offset / Update))

The challenge isn't simply to run a select statement in oracle with a limit and offset, as I can already do that through nested queries. The challenge is to get the select statement to work within an update statement, because even though the statement is syntactically correct, the Oracle parser fails to decode them. So far, nested queries (and Google) have failed me.

Has anyone else been having similar problems?

Community
  • 1
  • 1
Brian
  • 651
  • 1
  • 7
  • 13
  • 1
    If you upgrade to 12c, you might be able to do something like `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 offset 4 fetch next 1 row only);` – beldaz Sep 24 '13 at 03:20
  • Related, and probably useful if you have Oracle 12cR1 (or higher): http://stackoverflow.com/a/26051830/1461424 – sampathsris Sep 26 '14 at 10:41

1 Answers1

0

Deleted original answer, not viable

I feel this should be doable in a single SQL statement, but so far the combination of the need for a correlated subquery and the need for some sort of analytic function has made everything I tried fail.

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;
Dave Costa
  • 44,298
  • 8
  • 52
  • 70
  • Nope. the nested queries have the exact problem I mentioned earlier. The innermost query doesn't understand what ALF is, even though it's specified in the outermost query. SQL Error: ORA-00904: "ALF"."ASSETID": invalid identifier – Brian Sep 07 '11 at 12:12
  • @Brian -- sorry, of course you're right. I seem to always forget that issue until I actually run into it. – Dave Costa Sep 07 '11 at 13:46
  • I'm not exactly sure what the code does, but I've come up with a solution using the concepts you've shown. (LEAD function and using a stored procedure) Basically, I used a stored procedure that iterates through each entry in my table, using rownumber as an index. It searches for assetid = that row's assetid orders it, and gets the next contract using the lead function. I guess the jist of the solution is to use a stored procedure. – Brian Sep 07 '11 at 20:15