Insert with multiple selects

Question

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.

Answer 1

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.