CODEWITHSUNDEEP
sql-server-2008
user1830924
user1830924

Reputation: 75

SQL Server Table1 UPDATE Table2

Have two tables in a SQL Server database. Table #1 ClassicsRatings has RatingID, as key, TitleID, Rating, and Comment. Table #2, AVGRating, has TitleID, as key, AvgRating, and Count.

The following query updates Table #2 when the query is initially run on Table #1:

INSERT INTO AVGRating
   SELECT TitleID, Count(*) AS Count, AVG(Rating) AS AvgRating
   FROM ClassicsRatings
   GROUP BY TitleID
   ORDER BY TitleID

My problem is: how to get Table #2 to update when subsequent INSERTS are made into Table #1. I have tried MERGE and UPDATE as follows:

MERGE AVGRating AS AR
USING ( SELECT TitleID, COUNT(*) AS Count, AVG(Rating) AS AvgRating
FROM ClassicsRatings
GROUP BY RecipeID) AS ClassicsRatings(RecipeID, Count, AvgRating)
ON
(
AR.RecipeID = ClassicsRatings.RecipeID
)
WHEN NOT MATCHED THEN
   INSERT (RecipeID, Count, AvgRating)
   VALUES (ClassicsRatings.RecipeID, ClassicsRatings.Count, ClassicsRatings.AvgRating)
WHEN MATCHED THEN
   UPDATE
    SET AR.Count = ClassicsRatings.Count, 
        AR.AvgARating = ClassicsRatings.AvgRating;

UPDATE AVGRating
SET
AVGRating.TitleID = ClassicsRatings.TitleID,
AVGRating.AvgRating = ClassicsRatings.AvgRating,
AVGRating.Count = ClassicsRatings.Count
FROM AVGRating
INNER JOIN ClassicsRatings ON
AVGRating.TitleID = ClassicsRatings.TitleID
WHERE TitleID = TitleID

What I can't make happen is to INSERT into Table 1 and have Table 2 reflect that INSERT. Any help would be appreciated.

I had also used the following TRIGGER with no result:

CREATE TRIGGER AVGRatingTrigger
ON ClassicsRatings
AFTER INSERT AS
DECLARE @TitleID INT
DECLARE @Count INT
DECLARE @RatingAVG INT
BEGIN
UPDATE AVGRatings
WHERE AVGRatings.TitleID = ClassicsRatings.TitleID 
END

The trigger was changed to:

CREATE TRIGGER AVGRatingTrigger
ON ClassicsRatings
AFTER INSERT AS

BEGIN
UPDATE A
SET A.TitleID = I.TitleID,
A.Count = I.Count,
A.AvgRating = I.AvgRating
FROM AVGRating as A
INNER JOIN INSERTED as I ON A.TitelID = I.TitleID 
END

I got no error message with the trigger, however, when I INSERT a new rating into ClassicRatings, I get the error message "Invalid object AvgRating" It appears that the AvgRating part of AVG(Rating) AS AvgRating in the AVGRating table is not being recognized by the trigger (the initial query does recognize AvgRating). I think this would mean that since ClassicsRatings does not actually have AvgRating but does a calculation/aggregate and populates the AVGRating table with the calculation a conflict is happening. I have tried for two days to get this to work. My problem seems to be in the A.Count = I.Count A.AvgRating = I.AvgRating because Count in the ClassicsRatings table is actually COUNT(*) - not a field in ClassicsRatings and the AvgRating is really AVG(Rating) - not a field in ClassicsRatings. Any help would be greatly appreciated.

Upvotes: 0

Views: 250

Answers (1)

Petar Ivanov
Petar Ivanov

Reputation: 93020

You need to use triggers to achieve that. Simply create an "on insert" trigger, which runs the query when there is an insert to the first table.

In the trigger you should use the logical INSERTED, which contains the inserted rows. something like:

CREATE TRIGGER AVGRatingTrigger
ON ClassicsRatings
AFTER INSERT AS

BEGIN
UPDATE A
SET A.xxx = I.xxx,
    A.yyy = I.yyy,
    ...
FROM AVGRatings as A
INNER JOIN INSERTED as I ON A.TitleID = I.TitleID 
END

Upvotes: 1

Related Questions