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