【Oracle】重複データを削除する方法完全ガイド｜ROWID・ROW_NUMBER・CTAS・残す行の選択・再発防止まで解説

-- 特定列の組み合わせで重複している行を検出
SELECT email, COUNT(*) AS dup_count
FROM customers
GROUP BY email
HAVING COUNT(*) >= 2
ORDER BY dup_count DESC;

-- 複数列の組み合わせで重複検出
SELECT first_name, last_name, birth_date, COUNT(*) AS dup_count
FROM employees
GROUP BY first_name, last_name, birth_date
HAVING COUNT(*) >= 2;

-- 重複している実データを確認（削除前に必ず確認）
SELECT e.*
FROM employees e
WHERE (first_name, last_name) IN (
    SELECT first_name, last_name FROM employees
    GROUP BY first_name, last_name
    HAVING COUNT(*) >= 2
)
ORDER BY last_name, first_name, employee_id;

-- email が重複する行のうち、ROWID が最小の行だけ残す
DELETE FROM customers
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM customers
    GROUP BY email
);

-- 削除件数を確認してから COMMIT
-- SQL%ROWCOUNT で影響行数を確認
COMMIT;

-- first_name + last_name + birth_date が重複する行を削除
DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM employees
    GROUP BY first_name, last_name, birth_date
);

-- email の重複で、updated_at が最新の行を残す
DELETE FROM customers
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY updated_at DESC  -- 最新が 1 位
               ) AS rn
        FROM customers
    )
    WHERE rn > 1   -- 2 位以下を削除
);

-- created_at が最も古い行（最初に登録された行）を残す
DELETE FROM customers
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY created_at ASC   -- 最古が 1 位
               ) AS rn
        FROM customers
    )
    WHERE rn > 1
);

-- customer_id が最大（= 最後にINSERTされた行）を残す
DELETE FROM customers
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY customer_id DESC
               ) AS rn
        FROM customers
    )
    WHERE rn > 1
);

-- (1) 重複を除去した新テーブルを作成
CREATE TABLE customers_clean AS
SELECT * FROM (
    SELECT c.*,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
    FROM customers c
)
WHERE rn = 1;

-- (2) rn 列を削除
ALTER TABLE customers_clean DROP COLUMN rn;

-- (3) 元テーブルを退避してリネーム
ALTER TABLE customers RENAME TO customers_old;
ALTER TABLE customers_clean RENAME TO customers;

-- (4) インデックス・制約を再作成
-- ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);
-- CREATE INDEX idx_customers_email ON customers (email);

-- (5) 確認後に元テーブルを削除
-- DROP TABLE customers_old PURGE;

-- 全列が完全に同じ行のうち、ROWID が最小の行だけ残す
DELETE FROM customers a
WHERE ROWID > (
    SELECT MIN(ROWID) FROM customers b
    WHERE a.customer_id = b.customer_id
      AND a.email = b.email
      AND a.first_name = b.first_name
      AND NVL(a.phone, 'X') = NVL(b.phone, 'X')  -- NULL 対策
);

-- CTAS + DISTINCT の方がシンプル（全列完全一致の場合）
CREATE TABLE customers_clean AS
SELECT DISTINCT * FROM customers;

-- UNDO 溢れを防ぐため 10000 件ずつ削除
BEGIN
    LOOP
        DELETE FROM customers
        WHERE ROWID IN (
            SELECT rid FROM (
                SELECT ROWID AS rid,
                       ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id DESC) AS rn
                FROM customers
            )
            WHERE rn > 1
              AND ROWNUM <= 10000
        );

        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
    COMMIT;
END;
/

-- 重複削除後に UNIQUE 制約を追加
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

-- 複数列の組み合わせで UNIQUE
ALTER TABLE employees
ADD CONSTRAINT uq_emp_name_birth UNIQUE (first_name, last_name, birth_date);

-- UNIQUE インデックスとして作成する方法（同じ効果）
CREATE UNIQUE INDEX uq_customers_email ON customers (email);

DELETE FROM users
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_login DESC NULLS LAST) AS rn
        FROM users
    ) WHERE rn > 1
);
-- last_login が最新のユーザーを残す（NULL は最後）

-- order_no + order_date の組み合わせで重複 → order_id 最大を残す
DELETE FROM orders
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY order_no, order_date
                   ORDER BY order_id DESC
               ) AS rn
        FROM orders
    ) WHERE rn > 1
);

-- status='ACTIVE' の行を優先、同じなら updated_at 最新を残す
DELETE FROM accounts
WHERE ROWID IN (
    SELECT rid FROM (
        SELECT ROWID AS rid,
               ROW_NUMBER() OVER (
                   PARTITION BY account_no
                   ORDER BY DECODE(status, 'ACTIVE', 0, 1),  -- ACTIVE 優先
                            updated_at DESC
               ) AS rn
        FROM accounts
    ) WHERE rn > 1
);