Insert with multiple selects

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.

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_tableCROSS JOIN  (SELECT MAX(value0) AS max_value_0             FROM some_table) t

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