Updating with a subselect

------=_Part_14407_20031728.1208881062702
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?

------=_Part_14407_20031728.1208881062702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi, I need to update a field from a table based in a count.<br><br>This is
the query:<br><br><br>update&nbsp;&nbsp;&nbsp;
shops<br>set&nbsp;&nbsp;&nbsp; itemsqty = <br>&nbsp;&nbsp;&nbsp;
(<br>&nbsp;&nbsp;&nbsp; select &nbsp;&nbsp;&nbsp;
count(*)<br>&nbsp;&nbsp;&nbsp; from &nbsp;&nbsp;&nbsp; items
i1<br>&nbsp;&nbsp;&nbsp; join&nbsp;&nbsp;  &nbsp;&nbsp;  shops s1 on
i1.shopid = s1.shopid<br>
&nbsp;&nbsp;&nbsp; where&nbsp;&nbsp;  s1.shopid =
s0.shopid<br>&nbsp;&nbsp;&nbsp; )<br>from &nbsp;&nbsp;&nbsp; shops
s0<br><br><br>The problem I&#39;m having is that all the shops are updated
with the items quantity of the first shop.<br>I&#39;ve tried using the
count without a subselect, but PostgreSQL complains about it.<br>
<br>How can I do this?<br><br>

------=_Part_14407_20031728.1208881062702--