首页 新闻 赞助 找找看

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());

帮忙看下哪里出问题了

亲爱de小孩的主页 亲爱de小孩 | 初学一级 | 园豆:72
提问于:2014-07-25 16:36
< >
分享
最佳答案
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
吴瑞祥 | 高人七级 |园豆:29449 | 2014-07-25 16:54

那应该用什么呢

亲爱de小孩 | 园豆:72 (初学一级) | 2014-07-25 17:12

@亲爱de小孩: 应该想办法让他只出来一次`````

你这个肯定是业务逻辑有问题的,不可能一行数据满足2种判断的

你在From里面用distinct试一下

吴瑞祥 | 园豆:29449 (高人七级) | 2014-07-25 17:13

@吴瑞祥:用 distinct试一下,不报错了,但是这不是最佳的效果,我再改改

亲爱de小孩 | 园豆:72 (初学一级) | 2014-07-25 17:19
其他回答(1)
0

是你的id有重复,检查一下

To_tomorrow | 园豆:202 (菜鸟二级) | 2014-08-22 09:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册