Figured out one way to do it, perhaps I can get some feedback on if this
is the best way.. Thanks!
CREATE TEMP TABLE temp_ratings
(
RecipeId uuid,
Rating smallint,
CONSTRAINT id_pk PRIMARY KEY (RecipeId)
);
INSERT INTO temp_ratings(RecipeId, Rating)
SELECT RecipeId, Avg(Rating) as Rating FROM RecipeRatings GROUP BY
RecipeId;
UPDATE Recipes
SET Rating = tr.Rating
FROM temp_ratings as tr
WHERE Recipes.RecipeId = tr.RecipeId AND Recipes.Rating <> tr.Rating
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)
DROP TABLE #ratings
The error is:
ERROR: syntax error at or near "#"
LINE 3: INTO #ratings
^
********** Error **********
ERROR: syntax error at or near "#"
SQL state: 42601
Character: 53
Perhaps there's a different way to create temp tables? Even better is
if someone can re-write the query to not use the temp table, I'm far
from a SQL expert. Thanks!!
Mike
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
