SQL刪除沒有主鍵(Primary Key)的重複資料

一個沒有主鍵值(PK;Primary Key)的資料表是非常危險的事情,不僅會形成插入值出現重複的問題,在發現問題要移除重複資料時,往往也是一個工程。這篇文章就是在講述在「不完全刪除某特定重複性資料」的情況下,如何處理這個棘手的問題?

資料庫情境介紹

有一個會員資料表裡面有兩個欄位,分別為cID以及cName,從圖片中我們可以看到,這張資料表並沒有被指定任何PK。

果然,可能透過匯入或者是天兵式的人為輸入,產生了一大堆重複的人員。

我們可以透過SQL的COUNT以及HAVING,求取重複出現的資料列表。

SELECT cID, COUNT(*) AS iCount
FROM Member
GROUP BY cID
HAVING (COUNT(*) > 1)

問題至此大概已經形成,接著是刪除這些重複資料。

如何快速刪除重複的資料(SQL 2008以上)

SQL 2008以上可以利用「%%PHYSLOC%%」參數(Pysical Locations),拿到Data Row的儲存位址。因此我們可以透過這個方法,來進行重複資料刪除工作。指令如下:

DELETE Member
WHERE %%PHYSLOC%% > 
	(
		SELECT MIN(%%PHYSLOC%%)
		FROM Member AS Temp
		WHERE
			Temp.cID = Member.cID
			AND
			Temp.cID = 'A123' --針對王小明刪除
	)

值得注意的是,如果你沒有下「Temp.cID = 'A123'」這一行,此時SQL會把整張資料表,所有同一個cID超過一筆以上的資料,全部都移除喔!

如何快速刪除重複的資料(SQL 2005)

如果是比較舊的資料庫(SQL 2005),仍然使有機會快速刪除重複資料的。我們可以利用「ROW_NUMBER()」去拿出資料列排序,再利CTE(Common Table Expression)的方式去刪除。指令如下:

WITH Temp AS 
(
	SELECT ROW_NUMBER() OVER
	(
		PARTITION BY cID
		ORDER BY GETDATE()
	) AS iSort
	FROM Member
	WHERE cID = 'A123' --針對王小明刪除
)

DELETE FROM Temp
WHERE iSort > 1

值得注意的是,如果你沒有下「WHERE cID = 'A123'」這一行,此時SQL會把整張資料表,所有同一個cID超過一筆以上的資料(iSort > 1),全部都移除喔!

以上重複性資料刪除方式,提供給大家參考。

在SQL中查詢某個主鍵欄位值於資料表中重覆之語法 Microsoft SQLServer non-PrimaryKey DataDuplication Delete Remove KeepFirstRow KeepOneRow