MERGE 语句试图多次更新或删除同一行。目标行与多个源行匹配时会出现这种情况。MERGE 语句无法多次更新/删
0
悬赏园豆：100
[已解决问题]
解决于 2014-07-25 17:39
\
1 DECLARE @LatestSyncTime DATETIME 2 SET @LatestSyncTime = (SELECT MAX(SyncTime) FROM dbo.SyncLog WHERE 3 TargetTable = 'PlatformStatistics.dbo.VehicleHistoryRecordSummary' AND SyncStatus = 1); 4 5 MERGE INTO dbo.VehicleHistoryRecordSummary AS T 6 USING (SELECT vh.ID, pr.ID AS ProductID, pr.Name AS ProductName, vh.OBDID, vh.CreateTime AS RecordTime 7 FROM (SELECT * FROM SVRDB2_Diagnosis.dbo.VehicleHistoryRecordIndex 8 WHERE @LatestSyncTime IS NULL OR CreateTime > @LatestSyncTime) vh 9 10 INNER JOIN SVRDB1_Platform.dbo.us_UserOBD uo 11 On vh.OBDID=uo.OBDID 12 INNER JOIN SVRDB1_Platform.dbo.us_UserOrder us 13 ON uo.ZJID = us.ZJID 14 INNER JOIN SVRDB1_Platform.dbo.pr_Product pr 15 ON us.ProductID = pr.ID 16 ) AS S 17 ON T.ID = S.ID 18 WHEN MATCHED 19 THEN UPDATE 20 SET 21 T.ProductID = S.ProductID, 22 T.ProductName = S.ProductName, 23 T.OBDID = S.OBDID, 24 T.RecordTime = S.RecordTime, 25 T.UpdateTime = GETDATE() 26 WHEN NOT MATCHED 27 THEN INSERT ( 28 ID, ProductID, ProductName, OBDID, RecordTime, AddTime, UpdateTime) 29 VALUES (ID,ProductID, ProductName, OBDID, RecordTime,GETDATE(),GETDATE());
帮忙看下哪里出问题了
最佳答案
0
dbo.VehicleHistoryRecordSummary AS T 6 USING (SELECT vh.ID, pr.ID AS ProductID, pr.Name AS ProductName, vh.OBDID, vh.CreateTime AS RecordTime 7 FROM (SELECT * FROM SVRDB2_Diagnosis.dbo.VehicleHistoryRecordIndex 8 WHERE @LatestSyncTime IS NULL OR CreateTime > @LatestSyncTime) vh 9 10 INNER JOIN SVRDB1_Platform.dbo.us_UserOBD uo 11 On vh.OBDID=uo.OBDID 12 INNER JOIN SVRDB1_Platform.dbo.us_UserOrder us 13 ON uo.ZJID = us.ZJID 14 INNER JOIN SVRDB1_Platform.dbo.pr_Product pr 15 ON us.ProductID = pr.ID 16 ) AS S 17 ON T.ID = S.ID
你查一下这个on出来的结果,应该是VehicleHistoryRecordSummary表数据同一条会出来多次
收获园豆：100
那应该用什么呢
@亲爱de小孩: 应该想办法让他只出来一次`````
你这个肯定是业务逻辑有问题的,不可能一行数据满足2种判断的
你在From里面用distinct试一下
@吴瑞祥:用 distinct试一下，不报错了，但是这不是最佳的效果，我再改改
其他回答(1)
0
是你的id有重复，检查一下
