SEO

#1 (**permalink**) 04-24-2008, 06:17 PM

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?

#2 (**permalink**) 04-24-2008, 06:17 PM

Here is a little example to probe it:

-- Creating the tables
create table _shops(shopid serial primary key, name varchar(10),
itemsqty int);
create table _items(itemid serial primary key, shopid int references
_shops, name varchar(10));

-- Inserting test data
insert into _shops(name, itemsqty) values ('Shop 1', 0);
insert into _shops(name, itemsqty) values ('Shop 2', 0);
insert into _shops(name, itemsqty) values ('Shop 3', 0);
insert into _items(name, shopid) values ('Item 1', 1);
insert into _items(name, shopid) values ('Item 2', 1);
insert into _items(name, shopid) values ('Item 3', 1);
insert into _items(name, shopid) values ('Item 4', 2);
insert into _items(name, shopid) values ('Item 5', 3);
insert into _items(name, shopid) values ('Item 6', 3);

-- The following query leaves the itemsqty field of each row in the
_shops table with 3:

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 need that the query leaves the first shop with 3, the second with
1 and the last with 2

#3 (**permalink**) 04-25-2008, 12:43 AM

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

Put the subquery into your from clause:

update shops set itemsqty = newitemsqty
from shops, (
select s.shopid as shopid, count(*) as newitemsqty
from shops s join items i on i.shopid=s.shopid
group by s.shopid
) i
where shops.shopid = i.shopid

IMPORTANT:
notice that I didn't use an alias for the outer shops table. That won't
work when you're doing an update statement. You can't update a join,
only a table, so you have to use the where clause instead of join on ...

-Dennis

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Updating with a subselect