Is there a way to include a DELETE FROM statement in a nested WITH statement?

I am trying to delete duplicates by creating another table but I am unable to include the delete from statement. I have the code here in SQL Server but I am trying to convert it to Oracle. I tried replacing the select with the delete from but I’m getting the error: missing SELECT keyword.

Here is the code:

WITH RowNumCTE AS(
SELECT t.* ,
    ROW_NUMBER() OVER (
    PARTITION BY ParcelID,
                PropertyAddress,
                SalePrice,
                SaleDate,
                LegalReference
                ORDER BY
                    UniqueID
                    ) t
                    
FROM nashvillehousing t
--Order by parcelid;
) 
SELECT *
FROM RowNumCTE
WHERE t > 1;

Here is the sample data:

https://github.com/AlexTheAnalyst/PortfolioProjects/blob/main/Nashville%20Housing%20Data%20for%20Data%20Cleaning.xlsx

The expected result should be 104 deleted duplicate entries.

Answer

You can correlate on the ROWID pseudo-column:

DELETE FROM nashvillehousing
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (
             PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference
             ORDER BY UniqueID
           ) AS rn       
    FROM nashvillehousing
  )
  WHERE  rn > 1
)

If you really want to use a sub-query factoring clause (WITH) then:

DELETE FROM nashvillehousing
WHERE ROWID IN (
  WITH sqfc (rn) AS (
    SELECT ROW_NUMBER() OVER (
             PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference
             ORDER BY UniqueID
           )
    FROM nashvillehousing
  )
  SELECT ROWID
  FROM   sqfc
  WHERE  rn > 1
)