Re: Updating with a subselect

On Wed, 23 Apr 2008, Leandro Casadei wrote:

> 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?

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.