是否有一种方法包括DELETE FROM语句在嵌套的WITH语句?

Question

我试图通过创建另一个表来删除重复项，但我无法包含delete from语句。我有代码在这里的SQL服务器，但我正试图将其转换为Oracle。我试着用删除替换选择，但我得到错误:缺少select关键字。

代码如下:

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;

下面是示例数据:

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

预期结果应该是104个已删除的重复条目。

Answer 1

可以在ROWID伪列上进行关联:

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
)

如果你真的想使用子查询分解子句(WITH)，那么:

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
)