Lists: | pgsql-general |
---|
From: | "Leandro Casadei" <mateamargo(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Updating with a subselect |
Date: | 2008-04-22 16:17:42 |
Message-ID: | 5983afbb0804220917x3771415boc6299d0ba6d9f328@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updating with a subselect |
Date: | 2008-04-23 08:38:50 |
Message-ID: | 20080423083850.GF8401@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Leandro Casadei <mateamargo(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updating with a subselect |
Date: | 2008-04-23 08:42:16 |
Message-ID: | 20080423084216.GB16761@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Leandro Casadei <mateamargo(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updating with a subselect |
Date: | 2008-04-23 13:59:56 |
Message-ID: | 20080423065541.S588@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
)
From: | "Leandro Casadei" <mateamargo(at)gmail(dot)com> |
---|---|
To: | "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updating with a subselect |
Date: | 2008-04-23 14:38:03 |
Message-ID: | 5983afbb0804230738y6dcf6be7he7aa5e9935b51909@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Leandro Casadei <mateamargo(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updating with a subselect |
Date: | 2008-04-23 16:05:02 |
Message-ID: | 20080423085737.M4774@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.