14

I am trying to do insert to a table and it uses one select statement for one column. Below is the illustration of my query.

INSERT INTO MY_TBL (MY_COL1, MY_COL2)
VALUES (
(SELECT DATA FROM FIR_TABL WHERE ID = 1 AND ROWNUM = 1 ORDER BY CREATED_ON DESC),
1
);

It throws ORA-00907 Missing right Parenthesis. If I remove ORDER BY from this, it works as expected. But I need order it. How can I fix it?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Vaandu
  • 4,857
  • 12
  • 49
  • 75
  • 4
    I would question your data model if you "rely" upon the order of data inserted into a RDBMS table. The insert order should be irrelevent, you perform ordering when you query the data. – Ollie Feb 07 '12 at 11:56
  • What do you think the `ORDER BY` is going to do for you in this context? – Don Roby Feb 07 '12 at 11:59
  • I gave a example, there can be multiple rows for that select. Also, many columns are there in MY_TBL. Please check my update. – Vaandu Feb 07 '12 at 12:02
  • @Vanathi You added `rownum = 1`. This will return only one row. If you want to insert multiple rows at once, just remove that `rownnum` condition (and also `id` if that restricts rows), as `insert into ... select` supports multiple rows insertion – Jose Rui Santos Feb 07 '12 at 12:15
  • I need to add one row only. I'm getting latest data using order by and inserting it. – Vaandu Feb 07 '12 at 12:17

3 Answers3

23

Both the current answers ignore the fact that using order by and rownum in the same query is inherently dangerous. There is absolutely no guarantee that you will get the data you want. If you want the first row from an ordered query you must use a sub-query:

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
           from fir_tabl
          where id = 1
          order by created_on desc )
 where rownum = 1
       ;

You can also use a function like rank to order the data in the method you want, though if you had two created_on dates that were identical you would end up with 2 values with rnk = 1.

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
              , rank() over ( order by created_on desc ) as rnk
           from fir_tabl
          where id = 1)
 where rnk = 1
       ;
Ben
  • 51,770
  • 36
  • 127
  • 149
  • This seems to fix it for Oracle. Convert it to use an extra sub query and the "missing right parenthesis" (even though it is not missing a parenthesis) message goes away. Thanks! – rogerdpack Nov 07 '13 at 18:13
  • 1
    is there any reference about the fact that using order by and rownum in the same query is inherently dangerous? – jeromerg Nov 13 '16 at 08:30
  • I'm sure Tom Kate will have written something @jeromerg, but you should be able to see it from first principles. A heap table is stored, and therefore retrieved, unsorted. The ORDER BY clause is always the last operation to occur. Therefore the ROWNUM predicate will be used prior the ordering of an unordered set. You need to order first and because of the order of operation of a SQL statement the ordering must happen in an inner select (until 12c with (and only with) the FETCH N ROWS syntax) – Ben Nov 13 '16 at 09:55
1

You don't use a SELECT when using the VALUES keyword. Use this instead:

INSERT INTO MY_TBL (MY_COL)
SELECT DATA FROM FIR_TABL WHERE ID = 1 ORDER BY CREATED_ON DESC
;

Your edited query would look like:

INSERT INTO MY_TBL (MY_COL1, MY_COL2)
SELECT DATA, 1 FROM FIR_TABL WHERE ID = 1 AND ROWNUM = 1 ORDER BY CREATED_ON DESC
;
John Doyle
  • 7,475
  • 5
  • 33
  • 40
0

I agree that ordering should be performed when extracting data, not when inserting it.

However, as a workaround, you could isolate the ORDER BY clause from the INSERT incapsulating your whole SELECT into another SELECT.

This will avoid the error:

INSERT INTO MY_TABLE (
SELECT * FROM (
    SELECT columns
    FROM table
    ORDER BY clause
    )
)
xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
Marco Predicatori
  • 1