Thanks! You might be onto something, I see two potential problems though:
1) If the nested select returns no rows (no one has rated the recipe
before), it would try to set the value to null. The Rating column is
non-nullable which is the way I want it.
2) I'm not exactly 100% sure on this, but I think this query will end up
locking every row in the recipes table which could be tens of thousands,
and create some perf issues or deadlocks. Even though I run this query
once per day to update ratings, I'd like to keep it as streamlined as
possible..
Mike
Tino Wildenhain wrote:
Hi,
Mike Christensen wrote:
Hi guys, I'm in the process of migrating my database from MS SQL 2005
to PostgreSQL and there's one final stored proc that's giving me some
problems.. Perhaps someone can give me some help? Here's the sproc:
SELECT
RecipeId, Avg(Rating) as Rating
INTO #ratings
FROM RecipeRatings GROUP BY RecipeId
UPDATE Recipes
SET Rating = #ratings.Rating FROM Recipes INNER JOIN #ratings ON
(#ratings.RecipeId = Recipes.RecipeId AND #ratings.Rating <>
Recipes.Rating)
would not
UPDATE receipes
SET rating = r.rating
FROM (SELECT recipeid,avg(rating) as rating
GROUP BY recipeid) r
WHERE recipeid=r.recipeid
AND rating <> r.rating
work too w/o temp table?
(untested, can contain errors)
Tino
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
