如何使用CTE删除数据表中的重复行？解决ORA-00928缺失SELECT关键字错误

Hey there! Let's break down why you're hitting that ORA-00928: missing SELECT keyword error and fix it so you can delete those duplicate rows smoothly.

The Root Cause #

Your original CTE works perfectly for spotting duplicates, but Oracle doesn’t let you just swap out the SELECT statement with DELETE directly. The syntax for using a CTE with DELETE needs to explicitly target your base table and reference the CTE to filter which rows to remove—you can’t just replace the final SELECT with DELETE.

Correct Delete Query Using CTE #

First, let’s confirm your CTE correctly flags duplicates (you already have this part right!). To turn it into a delete operation, wrap the CTE and then use DELETE with a subquery to target rows where rn > 1 (these are your duplicate entries):

WITH RowNumCTE AS (
    SELECT ID,
           parcelid,
           propertyaddress,
           saledate,
           saleprice,
           legalreference,
           ROW_NUMBER() OVER (
               PARTITION BY parcelid, propertyaddress, saledate, saleprice, legalreference 
               ORDER BY id
           ) AS rn
    FROM housedata
)
DELETE FROM housedata
WHERE ID IN (SELECT ID FROM RowNumCTE WHERE rn > 1);

Alternative: Inline Subquery (No CTE) #

If you prefer a more compact approach, you can nest the row-number logic directly in the delete statement without a CTE:

DELETE FROM housedata
WHERE ID IN (
    SELECT ID
    FROM (
        SELECT ID,
               ROW_NUMBER() OVER (
                   PARTITION BY parcelid, propertyaddress, saledate, saleprice, legalreference 
                   ORDER BY id
               ) AS rn
        FROM housedata
    )
    WHERE rn > 1
);

Pro Tip Before Deleting! #

Always run a SELECT first to verify you’re targeting the right rows. Use your original CTE with a filter for rn > 1 to double-check which duplicates will be removed:

WITH RowNumCTE AS (
    SELECT ID,parcelid,propertyaddress,saledate,saleprice,legalreference,
           ROW_NUMBER() OVER (PARTITION BY parcelid,propertyaddress,saledate,saleprice,legalreference ORDER BY id) AS rn
    FROM housedata
)
SELECT * FROM RowNumCTE WHERE rn > 1;

This way you won’t accidentally delete rows you want to keep!

内容的提问来源于stack exchange，提问作者Mohammad Liton Hossain