Re: Updating with a subselect

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

IM****TANT:
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