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?