-- 1. 建立臨時表存放去重後資料
CREATE TABLE "#temp" AS (SELECT DISTINCT * FROM 目標表);
-- 2. 清空原表
TRUNCATE TABLE 目標表;
-- 3. 將去重資料寫回
INSERT INTO 目標表 SELECT * FROM "#temp";
-- 4. 刪除臨時表
DROP TABLE "#temp";
SELECT 欄位1, 欄位2, COUNT(*)
FROM 目標表
GROUP BY 欄位1, 欄位2
HAVING COUNT(*) > 1;
DELETE FROM 目標表 a
WHERE (a.欄位1, a.欄位2) IN (
SELECT 欄位1, 欄位2 FROM 目標表
GROUP BY 欄位1, 欄位2 HAVING COUNT(*) > 1
);
-- 建立重複鍵值臨時表
CREATE TABLE 臨時表 AS
SELECT 欄位1, 欄位2 FROM 目標表
GROUP BY 欄位1, 欄位2 HAVING COUNT(*) > 1;
-- 刪除時直接引用臨時表
DELETE FROM 目標表 a
WHERE (a.欄位1, a.欄位2) IN (SELECT 欄位1, 欄位2 FROM 臨時表);
Oracle 每列隱含 ROWID 唯一識別。保留 ROWID 最大(最新)的一筆:
-- 查詢除最新外的重複資料
SELECT a.rowid, a.*
FROM 目標表 a
WHERE a.rowid != (
SELECT MAX(b.rowid)
FROM 目標表 b
WHERE a.欄位1 = b.欄位1
AND a.欄位2 = b.欄位2
);
-- 刪除,保留最新
DELETE FROM 目標表 a
WHERE a.rowid != (
SELECT MAX(b.rowid)
FROM 目標表 b
WHERE a.欄位1 = b.欄位1
AND a.欄位2 = b.欄位2
);
-- 建立臨時表:每組關鍵欄位保留最大 ROWID
CREATE TABLE 臨時表 AS
SELECT a.欄位1, a.欄位2, MAX(a.ROWID) AS dataid
FROM 正式表 a
GROUP BY a.欄位1, a.欄位2;
-- 刪除非最大 ROWID
DELETE FROM 正式表 a
WHERE a.rowid != (
SELECT b.dataid FROM 臨時表 b
WHERE a.欄位1 = b.欄位1
AND a.欄位2 = b.欄位2
);
COMMIT;