Paste with multiple selections
I have a SQL query that returns some ora-01427 error:
single line 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 hardcoded values, I need a value from a select that only returns one row, and I want to do this for every row returned by the second selection.
It looks to me like this query would work if I only had one choice. Is there a way to do multiple SELECTs inside an INSERT? What will the syntax be?
EDIT: my_table and some_table are actually the same table, sorry for not being clear in the first place, in fact, I need value0 to be unique, so it needs to get the largest id every time, not just before insertion, but a new row is inserted every time.
source to share
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 respond to jarlh's post comment: "What if some_table = my_table and value0 need to increment every 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 ROW_NUMBER and it should work as is as well.
source to share
You can hide these two queries to one by cross-joining the query from some_table
with the results anoter_table
. You can also choose a hard coded literal.
Also, note that select
you don't need the keyword to insert the result values
:
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
source to share