SQL Server表1更新表2
在SQL Server数据库中有两个表。表#1 ClassicsRatings
具有RatingID
,作为密钥,TitleID
,Rating
和Comment
。表#2,AVGRating
,具有TitleID
,作为关键,AvgRating
和Count
。SQL Server表1更新表2
下面的查询更新表#2时查询最初的表#1运行:
INSERT INTO AVGRating
SELECT TitleID, Count(*) AS Count, AVG(Rating) AS AvgRating
FROM ClassicsRatings
GROUP BY TitleID
ORDER BY TitleID
我的问题是:如何让表#2更新时,后续插入被制成表#1 。我曾尝试MERGE
和UPDATE
如下:
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
我不能让发生是插入到表1和表有2反映INSERT。任何帮助,将不胜感激。
我还使用了下面的触发器没有结果:
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
触发改为:
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
我没有错误消息的触发,但是,当我插入一个新评级加入到ClassicRatings中,我收到错误消息“Invalid object AvgRating”看起来AVGRating表中的AVG(Rating)AS AvgRating的AvgRating部分没有被触发器识别(初始查询确认了AvgRating)。我认为这意味着由于ClassicsRatings实际上并不具有AvgRating,但是进行了计算/聚合,并且使用计算填充AVGRating表时发生了冲突。我试了两天才得到这个工作。我的问题似乎在A.Count = I.Count A.AvgRating = I.AvgRating,因为ClassicsRatings表中的Count实际上是COUNT(*) - 而不是ClassicsRatings中的字段,AvgRating实际上是AVG(Rating) - 不是ClassicsRatings中的一个字段。任何帮助将不胜感激。
您需要使用触发器来实现这一点。 只需创建一个“插入式”触发器,该插入式触发器在插入第一个表格时运行查询。
在触发器中,您应该使用包含插入行的逻辑INSERTED。 类似的东西:
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
谢谢你的回应。请参阅我对我的初始帖子的编辑。谢谢。 – user1830924 2013-02-12 19:02:07
非常感谢Petar。我想我可以从这里得到它。 – user1830924 2013-02-12 20:28:54
Petar,我错了,我无法得到它。如果你愿意看看我上面所做的编辑,如果你能理解我的编辑,我将不胜感激任何帮助。非常感谢你。 – user1830924 2013-02-15 20:31:53