Insert into Oracle sql developer-CodePudding

I want to ad column by using subquery.

Insert into Table_name
values (1, 'a', 'b', sysdate, sysdate   120, 'c',
        (Select number from other_table where column_name = 'x'), 2);

Error:

ORA-01427: single-row subquery returns more than one row.

How can I solve it?

CodePudding user response：

I think you want to use an INSERT INTO ... SELECT here. Note that generally you should always specify the target columns.

INSERT INTO Table_name (c1, c2, c3, c4, c5, c6, c7, c8)
SELECT 1, 'a', 'b', sysdate, sysdate   120, 'c', number, 2
FROM other_table
WHERE column_name = 'x';

CodePudding user response：

you need to force a single row

note the extra "ROWNUM" below

something like:

Insert into Table_name values(1,'a','b',sysdate,sysdate   120,'c',(Select number from other_table where column_name = 'x' and ROWNUM = 1),2);

see:https://blogs.oracle.com/connect/post/on-rownum-and-limiting-results