Обсуждение: more than one row returned by a subquery used as an expression
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/
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!
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