Postgres Pro
Downloads
Home   >   mailing lists

Thread: more than one row returned by a subquery used as an expression

more than one row returned by a subquery used as an expression

From
CSN
Date:
I'm trying to get this query to work:

update sectors set companies =(select companies from
industries where sector_id =sectors.id);

PG returns:

ERROR:  more than one row returned by a subquery used
as an expression

Column companies is just a count of rows in the
related companies table. Queries like this worked:

update industries set companies =(select count(id)
from companies where industry_id =industries.id);

I know I could do a join in the subselect, but I'm
curious why this doesn't work. If I do the subselect
by itself, it looks like all of the rows from the
industries table are return (I expected an error). Is
this the "implicit FROM" gotcha?

CSN



__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: more than one row returned by a subquery used as an expression

From
David Fetter
Date:
On Thu, Oct 13, 2005 at 05:05:56PM -0700, CSN wrote:
> I'm trying to get this query to work:
>
> update sectors set companies =(select companies from industries
> where sector_id =sectors.id);
>
> PG returns:
>
> ERROR:  more than one row returned by a subquery used as an
> expression

It's doing what it should.  Try the inner query for a few specific
examples of sector_id.

>
> Column companies is just a count of rows in the related companies
> table. Queries like this worked:
>
> update industries set companies =(select count(id) from companies
> where industry_id =industries.id);

The inner query is guaranteed to return exactly one row.  The other
one is not.

> I know I could do a join in the subselect, but I'm curious why this
> doesn't work.  If I do the subselect by itself, it looks like all of
> the rows from the industries table are return (I expected an error).
> Is this the "implicit FROM" gotcha?

Nope.  Thinko on your end.  Just make sure your inner query is
guaranteed to return exactly one row, and you'll be good. :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: more than one row returned by a subquery used as an

From
Alban Hertroys
Date:
CSN wrote:
> I'm trying to get this query to work:
>
> update sectors set companies =(select companies from
> industries where sector_id =sectors.id);

Try these instead:

update sectors
    set companies = industries.companies
   from industries
  where industries.sector_id =sectors.id;

update industries
    set companies = count(companies.id)
   from companies
  where industry_id = industries.id;

--
Alban Hertroys