Re: Updating with a subselect

am  Tue, dem 22.04.2008, um 13:17:42 -0300 mailte Leandro Casadei folgendes:
> 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

Try:

update shops set itemsqty = (select count(1) from items where shopid = shops.shopid);


test=*# select * from shops ;
 shop_id | itemsqty
---------+----------
       1 |        0
       2 |        0
(2 rows)

test=*# select * from items ;
 shopid
--------
      1
      1
      1
      2
(4 rows)

test=*# update shops set itemsqty = (select count(1) from items where shopid = shops.shop_id);
UPDATE 2
test=*# select * from shops ;
 shop_id | itemsqty
---------+----------
       1 |        3
       2 |        1
(2 rows)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net