Thread: Updating with a subselect

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


The problem I'm having is that all the shops are updated with the items quantity of the first shop.
I've tried using the count without a subselect, but PostgreSQL complains about it.

How can I do this?

am  Tue, dem 22.04.2008, um 13:17:42 -0300 mailte Leandro Casadei folgendes:
> 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

Try:

update shops set itemsqty = (select count(1) from items where shopid = shops.shopid);


test=*# select * from shops ;
 shop_id | itemsqty
---------+----------
       1 |        0
       2 |        0
(2 rows)

test=*# select * from items ;
 shopid
--------
      1
      1
      1
      2
(4 rows)

test=*# update shops set itemsqty = (select count(1) from items where shopid = shops.shop_id);
UPDATE 2
test=*# select * from shops ;
 shop_id | itemsqty
---------+----------
       1 |        3
       2 |        1
(2 rows)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Tue, Apr 22, 2008 at 01:17:42PM -0300, Leandro Casadei wrote:
> Hi, I need to update a field from a table based in a count.
>
> This is the query:

I don't know why your given query doesn't work, but you could simplify
it which may help.

> update    shops
> set    itemsqty =
>     (
>     select     count(*)
>     from     items i1
>     where  i1.shopid = s0.shopid
>     )
> from     shops s0

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

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
    )

On Wed, 23 Apr 2008, Leandro Casadei wrote:

> On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo <
> sszabo@megazone.bigpanda.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.

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?