5

I have a SQL request that return some ora-01427 error:

single-row subquery returns more than one row

INSERT INTO my_table (value0, value1, value2, value3) 
        VALUES((SELECT MAX(value0) FROM my_table), '5', (SELECT DISTINCT(value2) FROM another_table), '8');

The thing is, I need two hard-coded values, I need a value from a select that returns only one row, and I want to do that for each row returned by a second select.

I feel like this query would work if only I had only one select. Is there a way to do multiple SELECT inside an INSERT ? What would be the syntax ?

EDIT : my_table and some_table are actually the same table, sorry for not being clear in the first place, actually, I need value0 to be unique so it needs to retrieve the biggest id each time, not just before the insertion but every time a new row is inserted.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
vdolez
  • 977
  • 1
  • 14
  • 33
  • 1
    It's the second sub-select that returns more than one row. Do insert ... select instead. – jarlh Jun 02 '15 at 09:02

3 Answers3

4

You need to switch to INSERT/SELECT:

INSERT INTO my_table (value0, value1, value2, value3) 
SELECT DISTINCT (SELECT MAX(value0) FROM some_table), '5', value2, '8'
FROM another_table;

To answer your comment on jarlh's post: "What if some_table = my_table and value0 needs to be incremented each time a value is inserted ?"

INSERT INTO my_table (value0, value1, value2, value3) 
SELECT
   (SELECT MAX(value0) FROM my_table) 
     + ROWNUM -- ROW_NUMBER() OVER (ORDER BY whatever you need)
  ,'5'
  ,value2
  ,'8'
FROM
  (
    SELECT DISTINCT value2
    FROM another_table
  ) dt

Edit:

I switched to ROWNUM, but this is proprietary syntax. Oracle also supports Standard SQL's ROW_NUMBER and it should be working as-is, too.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks for the edit, I may have trouble understanding the OVER keyworld. Anyway, I tryied and get an id not unique error. Is there a way to get the value at execution ? – vdolez Jun 02 '15 at 09:38
  • All right, your method was a good trail to follow. Using the rownumber keyword instead of funciton worked great! – vdolez Jun 02 '15 at 09:56
  • If you replace "ROW_NUMBER() OVER (ORDER BY whatever you need)" by "rownum" I'll accept your answer :) – vdolez Jun 02 '15 at 11:58
  • @Vdolez: switched to `ROWNUM`, but the `ROW_NUMBER` syntax should also work (you must have done something else if it failed...) – dnoeth Jun 02 '15 at 12:11
  • Is it possible that the function is only executed before the treatment whereas the keyworld match the number of occurence during treatment ? – vdolez Jun 02 '15 at 12:24
  • 1
    @Vdolez: When you run the `SELECT` without the `INSERT` you will see the actual data. Assuming this is not the actual query something else might cause this error. – dnoeth Jun 02 '15 at 12:28
3

You can covert these two queries to a single one by cross joining the query from some_table with the results of anoter_table. The hard coded literal could also be selected.

Additionally, note that for inserting a select result you don't need the values keyword:

INSERT INTO my_table (value0, value1, value2, value3) 
SELECT      DISTINCT max_value_0, '5', value2, '8'
FROM        another_table
CROSS JOIN  (SELECT MAX(value0) AS max_value_0
             FROM some_table) t
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

Do INSERT with SELECT instead:

INSERT INTO my_table (value0, value1, value2, value3)
    SELECT DISTINCT (SELECT MAX(value0) FROM some_table), 5, value2, 8
    FROM another_table
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • What if some_table = my_table and value0 needs to be incremented each time a value is inserted ? – vdolez Jun 02 '15 at 09:18
  • Ok, so my_table = some_table. Value0 is an ID and must be unique. Each time a value is inserted in my_table, I need SELECT MAX(value0) to be updated. – vdolez Jun 02 '15 at 09:20