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?
