代码之家 › 专栏 › 技术社区 › mangesh

重复记录的SQL更新

sql-server

mangesh · 技术社区 · 8 年前

我想使用对primaryid的duplicateid ref将contact(contactactid)表的accountid更新为原始accountid

以下是我的选择查询:

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

更新了contactID的必需数据:

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

1 回复 | 直到 8 年前

Pavel Nefyodov 8 年前

       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