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