| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Dreaded ORA-01427: single-row subquery returns more than one row error
I am trying to rewrite the following Access generated query:
UPDATE DISTINCTROW SARDOWN INNER JOIN [OB-ACCRUED] ON
(SARDOWN.SHIPTO = [OB-ACCRUED].SHIPTO) AND
(SARDOWN.SHIPFROM = [OB-ACCRUED].SHIPFROM) AND
(SARDOWN.CARRIER = [OB-ACCRUED].ABV) SET SARDOWN.ACCRUED =
[OB-ACCRUED].[ACCRUED];
I rewrote this query in SQL*PLUS as follows:
Update Frt.Sardown a
Set accrued =
(Select accrued from frt.OB_accrued b
where (a.shipto = b.shipto
and a.shipfrom = b.shipfrom
and a.carrier = b.abv))
As I just learned, when Access does not find a match on OB- ACCRUED, ir will leave the SARDOWN fields untouched, but Oracle will NULL the SARDOWN fields when it does not find a match on OB-ACCRUED.
As I have done with a few simpler queries, I have added a WHERE clause to the query so that it only attempts to process rows where a match is found and does not process rows where a match is not found, leaving the SARDOWN columns intact instead of NULLing them. The complete query looks like this:
Update Frt.Sardown a
Set accrued =
(Select accrued from frt.OB_accrued b
where (a.shipto = b.shipto
and a.shipfrom = b.shipfrom
and a.carrier = b.abv))
This logic has worked fine with other queries in the past,
but this time, I am getting the error:
"ORA-01427: single-row subquery returns more than one row"
on the "(select abv from frt.ob_accrued b" statement
It IS a valid error...there ARE multiple rows being returned as a result.
My question is: how would I write this query so that I mimic the behavior of the original Access query above.
Hope all of this makes some sense...thanks alot for reading and responding!
Andy
 |
 |