update with correlated query

gistfile1.sql

-- I had an existing products table, and needed to denormalize two fields for faster sorting.
-- The number of rates and total rating is currently in a ratings table, and needs to be copied to products

create table products (
 id integer, 
 name varchar(16), 
 ratings_count integer,
 total_rating integer
);

create table ratings (
 product_id integer, 
 rating integer
);

insert into products values 
 (1,'apple',0,0),
 (2,'orange',0,0),
 (3,'banana',0,0),
 (4,'natto',0,0);

insert into ratings values
 (1,1),
 (1,1),
 (1,2),
 (2,3),
 (3,4),
 (3,5);


-- update ratings_count
update products set ratings_count = 
 (select count(*) from ratings where products.id=ratings.product_id) 

-- update total_ratings
update products set total_rating = 
 coalesce((select sum(rating) from ratings where products.id=ratings.product_id),0)