Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Insert with select subquery

698053Sep 15 2010 — edited Sep 15 2010
Hi all,
I've this puzzle.

TableA and tableB
select max(a.ID_PART) 
from tableA a, tableB b 
where a.CDA_PART=b.CDA_PART
group by b.CDA_PART;
obtain ID_PART from A when match CDA_PART
The max() because may be multiple ID_PART for one CDA_PART... so I want the max value.

The problem comes when I put this subquery select into a INSERT statement that populate the B table with corrispondent max(ID_PART) on CDA_PART matching.
INSERT INTO tableC (ID_LOAD,  CDA_PART, ID_PART)
SELECT ID_LOAD, CDA_PART, (select max(a.id_part) from tableA a, tableB b 
                                          where a.CDA_PART=b.CDA_PART   
                                          group by b.cda_part)
FROM tableB
WHER ID_LOAD>5000;
the error message: ORA-01427: single-row subquery returns more than one row

I understand that select subquery returns multiple row, but I don't know how to correct the problem to insert for each CDA_PART of tableB the corrispondent max value of ID_PART of tableA

Any suggestions?
This post has been answered by Frank Kulash on Sep 15 2010
Jump to Answer

Comments

Anurag Tibrewal Sep 15 2010
Hi,

Try this
INSERT INTO tableC (ID_LOAD,  CDA_PART, ID_PART)
SELECT ID_LOAD, CDA_PART, (select max(a.id_part) from tableA a
                                          where a.CDA_PART=b.CDA_PART   
                                          group by b.cda_part)
FROM tableB
WHER ID_LOAD>5000;
I have removed tableB b from the where clause as it is not appropriate there.

Some other suggestion.
1) It is unnecessary to write group by b.cda_part as there would be only one cda_part whenever this query is executed.

Regards
Anurag
user503699 Sep 15 2010
trebbia wrote:
obtain ID_PART from A when match CDA_PART
The max() because may be multiple ID_PART for one CDA_PART... so I want the max value.
Any suggestions?
Because the subquery can return multiple rows, one for each unique value of b.cda_part.
698053 Sep 15 2010
Anurag Tibrewal wrote:
Hi,

Try this
INSERT INTO tableC (ID_LOAD,  CDA_PART, ID_PART)
SELECT ID_LOAD, CDA_PART, (select max(a.id_part) from tableA a
where a.CDA_PART=b.CDA_PART   
group by b.cda_part)
FROM tableB
WHER ID_LOAD>5000;
I have removed tableB b from the where clause as it is not appropriate there.

Some other suggestion.
1) It is unnecessary to write group by b.cda_part as there would be only one cda_part whenever this query is executed.

Regards
Anurag
Hi anurag,
I tried your modify... but now not is not selected the max of a.id_part
Frank Kulash Sep 15 2010
Hi,

One way is to have a correlated sub-query, like this:
INSERT INTO tableC (ID_LOAD,  CDA_PART, ID_PART)
       SELECT       ID_LOAD,  CDA_PART, ( SELECT  MAX (a.id_part)
       		    	      		  FROM	  tablea
                                          WHERE   cda_part	= b.cda_part   
                                        )
	FROM    tableB	b
	WHERE 	ID_LOAD	> 5000	
;
If you'd like to post some sample data (CREATE TABLE and INSERT statments for the tables as they exist before this INSERT), and the results you want in tableC after this INSERT, then I could test it.

The statement above will INSERT one row for every row that is already in tableB.
The scalar sub-query (FROM tableA) uses an aggreagate function (MAX) but no WHERE clause, so it is guaranteed to return exactly 1 row.
698053 Sep 15 2010
Sorry Anurag my mistake,
your code run right!!!!
INSERT INTO tableC (ID_LOAD,  CDA_PART, ID_PART)
SELECT ID_LOAD, CDA_PART, (select max(a.id_part) from tableA a
where a.CDA_PART=b.CDA_PART   
group by b.cda_part)
FROM tableB
WHER ID_LOAD>5000;
I have removed tableB b from the where clause as it is not appropriate there.
Now I have a performance problem because tableA=47,000 rows, tableB=1,800,000 rows
and in a test for only 10,000 rows of tableB the elapse time is 1'30'' :(

I believe it, the subquery select is executed for each row on INSERT statement.
How may be re-write the code for faster performance?
698053 Sep 15 2010
CREATE TABLE TABLEA
(
  ID_PART   INTEGER,
  CDA_PART  CHAR(20 BYTE)
);

CREATE TABLE TABLEB
(
  ID_LOAD   INTEGER,
  CDA_PART  CHAR(20 BYTE),
);

CREATE TABLE TABLEC
(
  CDA_PART_FROM_B  CHAR(20 BYTE),
  ID_PART_FROM_A   INTEGER
);

INSERT INTO TABLEA VALUES(1, 57511);
INSERT INTO TABLEA VALUES(2, 78403);
INSERT INTO TABLEA VALUES(3, 11617);
INSERT INTO TABLEA VALUES(4, 75148);
INSERT INTO TABLEA VALUES(5, 58571);
INSERT INTO TABLEA VALUES(6, 78403);
INSERT INTO TABLEA VALUES(7, 58571);

INSERT INTO TABLEB VALUES(101, 57511);
INSERT INTO TABLEB VALUES(102, 58571);
INSERT INTO TABLEB VALUES(103, 11617);
INSERT INTO TABLEB VALUES(104, 78403);
INSERT INTO TABLEB VALUES(105, 57511);
I should insert into TableC for each CDA_PART values of tableB the corrispondent ID_PART from tableA
and when on tableA match the same code (see 78403 and 58571) select max of ID_PART (6 and 7):
CDA_PART   ID_PART
57511          1
58571          7
11617          3
78403          6
57511          1
Frank Kulash Sep 15 2010
Hi,

For the example you posted, you don't want a WHERE clause. Perhaops you do need something like that for your real data.
Aside from that, and changing the column names, the statement I posted earlier should work:
INSERT INTO tableC (cda_part_from_b,  id_part_from_a)
       SELECT       cda_part      ,  ( SELECT  MAX (id_part)
       		    	               FROM    tablea
                                       WHERE   cda_part	= b.cda_part   
                              	     )
	FROM    tableB	b
--	WHERE 	id_load	> 5000		-- You may need something like this later
;
698053 Sep 15 2010 — edited on Sep 15 2010
Hi Frank,
yes I don't want WHERE clause ID_LOAD>5000. Delete this clause

However was not clause where the problem but the subquery select in the INSERT statement.
INSERT INTO tableC (cda_part_from_b,  id_part_from_a)
       SELECT       cda_part      ,  ( SELECT  MAX (id_part)
       		    	               FROM    tablea
                                       WHERE   cda_part	= b.cda_part   
                              	     )
	FROM    tableB	b
this code run right, but is very slow because the subquey select MAX(id_part) is executed for each CDA_PART of tableB... and tableB has 1,800,000 rows

Edited by: trebbia on 15-set-2010 21.44
Frank Kulash Sep 15 2010
Answer
Hi,

This should be faster, since it avoids the scalar sub-query:
INSERT INTO tableC (cda_part_from_b,  id_part_from_a)
WITH   got_max_id_part	AS
(
	SELECT	  cda_part
	,	  MAX (id_part)	AS max_id_part
	FROM	  tablea
	GROUP BY  cda_part
)
SELECT	b.cda_part, a.max_id_part
FROM		 tableb           b
LEFT OUTER JOIN  got_max_id_part  a   ON   a.cda_part	= b.cda_part
;
If you know that every cda_part in tableb is also in tablea, then jou can use an inner join rather than an outer join; that should make it a faster still.
Marked as Answer by 698053 · Sep 27 2020
698053 Sep 15 2010
Great Frank!!
with LEFT OUTER JOIN is more slow...
but using INNER JOIN is very fast!
Yes I'm sure that each tableB.cda_part is on tablea.cda_part

Thanks!!!
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 13 2010
Added on Sep 15 2010
10 comments
2,111 views