Re: Dreaded ORA-01427: single-row subquery returns more than one row error

Use the "exists" clause instead. i.e.

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))
 where exists (select null from frt.ob_accrued b
       where (a.shipto = b.shipto
              and a.shipfrom = b.shipfrom
              and a.carrier = b.abv))

The where clause will limit the update to those rows that have a successful subquery.

abrusko wrote in message <934371993.11724_at_www.remarq.com>...
>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))
>where a.shipto =
>(select shipto from frt.ob_accrued b
> where a.shipto = b.shipto)
>and a.shipfrom =
>(select shipfrom from frt.ob_accrued b
> where a.shipfrom = b.shipfrom)
>and a.carrier =
>(select abv from frt.ob_accrued b
>where 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
>
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network
*
>The fastest and easiest way to search and participate in Usenet - Free!
Received on Thu Aug 12 1999 - 06:04:12 CDT