Re: How can I avoid the "Single row subquery returns more than one row" message

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 07 Apr 2000 18:49:09 +0800
Message-ID: <38EDBD25.2D91@yahoo.com>

Andy wrote:
>
> Hi,
>
> I am trying to do a simple (hopefully) join between 2 tables and
> use a column in table 2 to update table 1. The problem is that
> my subquery is returning multiple rows. Is there a way to force
> the first match to be used by default to avoid the "multiple
> rows" situation, or any other way around this problem?
> Hope this makes sense...the query that is having the problem is
> here:
>
> Update Frt.Mpay_working a
> Set shipper_city =
> (Select b.city from Frt.cities b
> where a.shipfrom = b.shipto);
>
> Thanks alot for your help...appreciate it!
> 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!

Typically this indicates that your data or query is incorrect - but assuming you have checked these you can always append

  'and rownum < 2'

to your sub-select to get the first matching row.

HTH
--

---

Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse Received on Fri Apr 07 2000 - 05:49:09 CDT