CODEWITHSUNDEEP
sqloracle-databasesql-insert
NOOB
NOOB

Reputation: 23

ORA-01427 error , insert into all rows

My query keeps returning an ORA-01427 error, how to resolve it please:

I have 3 tables: T1 with (idA,B)columns, B type date T2 with (idC,D)columns, D type varchar T3 with (idE,F,G,....)columns, F type date, G type varchar, idE(DBsquences),F and G are Primary Key.

I want to insert into T3, data from T1 and T2

My query: 

insert into T3 (F, G) values ((select B from T1 where max(B)), ( select D from T2));

I get: ORA-01427 error Single-row subquery returns more than one row.

How can i do this with cursor?

T1:

idA   B   
------------
1     date1      
2     date2  (the max date)     
3     date3 

T2:

idC    D    
----------
1     x       
2     y  
3     z
.     .
.     .
.     .
n     N

Result must be:

T3:

idE    F          G
-----------------------
1     date2       y
2     date2       z
.       .         .
.       .         .
.       .         .
.       .         .
n     date2       N

Upvotes: 2

Views: 583

Answers (1)

Tatiana
Tatiana

Reputation: 1499

If I understand you right:

INSERT INTO T3 (F, G)
SELECT  (select max(B) from T1), D
FROM T2

If you insert values from select, you don't need values key word, but you should use only 1 query. And it is not valid condition - where max(B). You can't use group functions in WHERE, only HAVING. You should make a condition like max(B) = something. If you want to chose max value, you just use function max in SELECT

Upvotes: 2

Related Questions