問題描述

我有一個生產數據庫，偶爾需要合并"單個表中的冗余行.

I have a production database where occasionally redundant rows in a single table need to be "Merged".

假設此表中的兩行具有相同的值，除了它們的 ID.

Let's assume that both rows in this table have identical values, except their IDs.

Table "PrimaryStuff"
ID | SomeValue
1  | "I have value"
2  | "I have value"
3  | "I am different"

我們還假設存在多個相關表.因為在PrimaryStuff"表中創建了重復項，所以通常在這些子表中創建的行都應該與 PrimaryStuff 表上的單個記錄相關.這些表的數量和名稱不在我的控制之下，應該在運行時動態考慮. IE:我不知道名稱甚至相關記錄的數量，因為其他人可能會編輯數據庫在我不知情的情況下.

Let's also assume that a number of related tables exist. Because duplicates were created in the "PrimaryStuff" table, often rows are created in these child tables that SHOULD all be related to a single record on the PrimaryStuff table. The number and names of these tables are not under my control and should be considered dynamically at runtime. IE: I don't know the names or even the number of related records, as other people may edit the database without my knowledge.

Table "ForeignStuff"
ID | PrimaryStuffId | LocalValue
1| 1| "I have the correct FK"
2| 1| "I have the correct FK"
3| 2| "I should get pointed to an FK of 1"

為了解決 PrimaryStuff 的第 1 行和第 2 行的重復問題，我希望所有相關的表都將它們的 FK 更改為 1，然后刪除 PrimaryStuff 的第 2 行.這應該是微不足道的，就好像 PrimaryStuff 的行一樣1 不存在，我只需將第 2 行的主鍵更新為 1，更改就會級聯出來.我不能這樣做，因為這將是 PrimaryStuff 的唯一索引中的重復鍵.

To resolve the duplication of PrimaryStuff's row 1 and 2, I wish to have ALL related tables change their FK's to 1s and then delete the PrimaryStuff's row 2. This SHOULD be trivial, as if PrimaryStuff's row 1 didn't exist, I could just update the Primary Key on Row 2 to 1, and the changes would cascade out. I cannot do this because that would be a duplicate key in the PrimaryStuff's unique index.

請隨時提出問題，我會盡力解決任何令人困惑的問題.

Feel free to ask questions and I'll try to clear up anything that's confusing.

推薦答案

首先讓我們獲取需要更新的行列表(據我所知，您希望最低 ID 替換所有較高 ID)

First lets get a list of the rows that need to be updated (as I understand it you want the lowest ID to replace all the higher IDs)

 SELECT MIN(ID) OVER (PARTITION BY SomeValue ORDER BY SomeValue, ID ASC) AS FirstID,
        ID,
        SOMEVALUE
 FROM PrimaryStuff

我們可以去掉 FirstID 和 ID 匹配的那些，這些都沒有關系

We can remove the ones where FirstID and ID match, these don't matter

SELECT FirstID, ID FROM
(
 SELECT MIN(ID) OVER (PARTITION BY SomeValue ORDER BY SomeValue, ID ASC) AS FirstID,
        ID,
        SOMEVALUE
 FROM PrimaryStuff
) T
WHERE FirstID != ID

現在我們有一個更改列表.我們可以在更新語句中使用它，將其放入臨時表(或我在下面所做的 CTE)中:

Now we have a change list. We can use this in an update statement, put it in a temp table (or a CTE as I did below):

WITH ChangeList AS
(
  SELECT FirstID, ID FROM
  (
   SELECT MIN(ID) OVER (PARTITION BY SomeValue ORDER BY SomeValue, ID ASC) AS FirstID,
        ID
   FROM PrimaryStuff
  ) T
  WHERE FirstID != ID
)
UPDATE ForeignStuff
SET PrimaryStuffId = ChangeList.FirstID
FROM ForeignStuff
JOIN ChangeList ON ForeignStuff.ID = ChangeList.ID

注意 - 代碼未經測試，可能有錯別字.

NB - Code not tested, might have typos.

這篇關于T-SQL“合并"兩行，或“Rekey"所有 FK 關系的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！