时间戳

首页 > 解决方案 > 重复记录的 SQL 更新

sql-server - 重复记录的 SQL 更新

问题描述

我想使用 DuplicateId ref 使用原始 AccountID 更新联系人（ContactActID）表的 AccountID 到 PrimaryId

以下是我的选择查询：

SELECT  AD.primaryid, 
        AD.actID, 
        AD.accountname, 
        AD.duplicateID, 
        C.Actid AS [ContactActID],
        C.ContactId,
        C.Contactname 
FROM  Contact09072018 C
INNER JOIN AccountDump AD ON C.actid=AD.actid
ORDER BY AD.primaryid

上述查询的结果：

primaryid  actID          accountname            duplicateID ContactActID  ContactId Contactname 
1          2499   A SERVICE INDIA PRIVATE LIMITED   0               2499    2879    Rajesh 
1          2499   A SERVICE INDIA PRIVATE LIMITED   0               2499    3063    Deepak 
1          2499   A SERVICE INDIA PRIVATE LIMITED   0               2499    3064    Rajesh Aiwale
2          22999  A SERVICE INDIA PVT LIMITED       1               22999   24245   Dinesh 
3          4484   A Service India Pvt Ltd           1               4484    5213    Siddharth 
3          4484   Ae Service India Pvt Ltd          1               4484    7008    Rajesh 
4          11524  An Pvt Ltd                        0               11524   12514   Abhijit 
5          22963  Ansa Pvt Ltd.                     4               22963   24204   Mr Abhijit
6          5059   Arm                               7               5059    5872    Mafasal 
7          22992  Arm industries                    0               22992   24204  Muffazzal

更新后的 ContactActID 所需的数据：

primaryid actID          accountname            duplicateID ContactActID  ContactId Contactname 
1   2499    A SERVICE INDIA PRIVATE LIMITED     0           2499            2879    Rajesh 
1   2499    A SERVICE INDIA PRIVATE LIMITED     0           2499            3063    Deepak 
1   2499    A SERVICE INDIA PRIVATE LIMITED     0           2499            3064    Rajesh 
2   22999   A SERVICE INDIA PVT LIMITED         1           2499            24245   Dinesh 
3   4484    A Service India Pvt Ltd             1           2499            5213    Siddharth 
3   4484    Ae Service India Pvt Ltd            1           2499            7008    Rajesh 
4   11524   An Pvt Ltd                          0           11524           12514   Abhijit 
5   22963   Ansa Pvt Ltd.                       4           11524           24204   Mr Abhijit
6   5059    Arm                                 7           22992           5872    Mafasal 
7   22992   Arm industries                      0           22992           24238 Muffazzal

标签： sql-server

解决方案

领取方式

[联系方式ID]

需要改变

       SELECT  AD.primaryid, 
        AD.actID, 
        AD.accountname, 
        AD.duplicateID, 
        CASE 
        WHEN AD.duplicateID=0
        THEN C.Actid
        ELSE (SELECT DISTINCT t2.actID FROM AccountDump t2 WHERE t2.primaryid=AD.duplicateID) 
        END
        AS [ContactActID],
        C.ContactId,
        C.Contactname FROM  Contact09072018 C
INNER JOIN AccountDump AD ON C.actid=AD.actid
ORDER BY AD.primaryid

推荐阅读