On Wed, 23 Apr 2008, Leandro Casadei wrote: > On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo < > sszabo@xxxxxxxxxxxxxxxxxxxxx> wrote: > > > On Tue, 22 Apr 2008, Leandro Casadei wrote: > > > > > Hi, I need to update a field from a table based in a count. > > > > > > This is the query: > > > > > > > > > update shops > > > set itemsqty = > > > ( > > > select count(*) > > > from items i1 > > > join shops s1 on i1.shopid = s1.shopid > > > where s1.shopid = s0.shopid > > > ) > > > from shops s0 > > > > I think you'll actually want something simpler. The following might do > > what you want. > > > > update shops > > set itemsqty = > > ( > > select count(*) > > from items i1 > > where i1.shopid = shops.shopid > > ) > > > > > Yes, thanks. I've received a similar answer in the PostgreSQL Forums. > I don't know why the join did't work. > > I had to do this with another table, and the subselect needed a few joins, > but I have replaced them with the table names separated by commas and it > worked too. > > Might this be some kind of bug? I don't think so. It's just an unconstrained join. If you were to think about the select that the original update would be like, it'd be like: select (select count(*) from items i1 join shops s1 on i1.shopid=s1.shopid where s1.shopid = s0.shopid) from shops, shops s0; So, it's an unconstrained join of shops and s0. In theory, I think you could have also made the select work by adding a WHERE s0.shopid=shops.shopid, but since there is a much simpler version for that case, it seemed to make more sense to give the simplified one.
