Re: Updating with a subselect

On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo <
sszabo(at)megazone(dot)bigpanda(dot)com> 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?