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

IMO it looks like your cities table has a problem, as to me it looks like the data ought to be unique and it isn't. That said you would think (select distinct b.city etc... would eliminate the duplicates.
If that still doesn't work your cities table has different names for the same shipto and you to clean it out anyway.

Hth,

Sybrand Bakker, Oracle DBA

"Andy" <abruskoNOabSPAM_at_binney-smith.com.invalid> wrote in message news:001402bc.07df25e8_at_usw-ex0105-037.remarq.com...
> 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!
>
Received on Fri Apr 07 2000 - 12:10:50 CDT