Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql General > Updating with a...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 6 Topic 15356 of 17598
Post > Topic >>

Updating with a subselect

by mateamargo@[EMAIL PROTECTED] ("Leandro Casadei") Apr 22, 2008 at 01:17 PM

------=_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--
 




 6 Posts in Topic:
Updating with a subselect
mateamargo@[EMAIL PROTECT  2008-04-22 13:17:42 
Re: Updating with a subselect
andreas.kretschmer@[EMAIL  2008-04-23 10:38:50 
Re: Updating with a subselect
kleptog@[EMAIL PROTECTED]  2008-04-23 10:42:16 
Re: Updating with a subselect
sszabo@[EMAIL PROTECTED]   2008-04-23 06:59:56 
Re: Updating with a subselect
sszabo@[EMAIL PROTECTED]   2008-04-23 09:05:02 
Re: Updating with a subselect
mateamargo@[EMAIL PROTECT  2008-04-23 11:38:03 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Mon Dec 1 8:31:22 CST 2008.