Here is a little example to probe it:
-- Creating the tables
create table _shops(shopid serial primary key, name varchar(10),
itemsqty int);
create table _items(itemid serial primary key, shopid int references
_shops, name varchar(10));
-- Inserting test data
insert into _shops(name, itemsqty) values ('Shop 1', 0);
insert into _shops(name, itemsqty) values ('Shop 2', 0);
insert into _shops(name, itemsqty) values ('Shop 3', 0);
insert into _items(name, shopid) values ('Item 1', 1);
insert into _items(name, shopid) values ('Item 2', 1);
insert into _items(name, shopid) values ('Item 3', 1);
insert into _items(name, shopid) values ('Item 4', 2);
insert into _items(name, shopid) values ('Item 5', 3);
insert into _items(name, shopid) values ('Item 6', 3);
-- The following query leaves the itemsqty field of each row in the
_shops table with 3:
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
-- I need that the query leaves the first shop with 3, the second with
1 and the last with 2
|
