Insert into Oracle sql developer

Question

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?

Depends on the number column. Please share some sample data. — Shmiel
– Shmiel, Commented Jul 11, 2022 at 19:38

Tim Biegeleisen · Accepted Answer · 2022-07-11 19:43:19Z

2

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';

answered Jul 11, 2022 at 19:43

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

granadaCoder · Accepted Answer · 2022-07-11 19:44:23Z

0

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

edited Jul 11, 2022 at 19:44

answered Jul 11, 2022 at 19:38

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

4 Comments

Shmiel Over a year ago

Just getting the top one would not be the best idea, maybe the answer is to limit it on another where condition.

granadaCoder Over a year ago

sorry. it should be where/and, instead of 2 wheres.

astentx Over a year ago

This query will not resolve the issue, but just remove an error. Because it will return an arbitrary row and will never fail even in case of change in the data model, making it hard to locate the problematic code.

granadaCoder Over a year ago

Then your where clause on your "inner-select" needs to be more specific. If you are trying to find a specific ROW with your "inner-select" you need to add more information in your question.

Collectives™ on Stack Overflow

Insert into Oracle sql developer

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related