Geek Answers Handbook

MySQL Error for Oracle Syntax (Constraint / Offset / Update)

I have a MySQL query that works in my current MySQL database. I was forced to go to the oracle, so I am trying to port all my stored procedures / programs to use Oracle SQL syntax. I have a lot of problems with one specific request. Here is a MySQL query. It updates the 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 cannot use the limit command, so I was looking for a workaround. Here is my oracle request. (which does not 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:

  AND alf2.lasttradedate > alf.lasttradedate

Removing the update statement and adding some fake values ​​to the subquery gives 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 alf link is not working. Any idea how I can modify my query to make it work in oracle?

+4
source share
2 answers

The parser doesn't seem to like it, even though it is syntactically correct. Probably the two implicit and ordered reservations somehow blind him. I reproduced this.

You can use the analytic 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
+2
source

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

0
source

More articles:


All Articles