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