I am writing this query using windows function Row_Number() which will find out duplicates and i am trying to delete those duplicates.

To do this i have written CTE and included window function it and attempting to delete duplicate row. However, i am getting error saying delete is not updatable.

select * from housingdata;
.
.
.
with rownumcte as (
select * ,row_number() over (partition by ParcelID, PropertyAddress, 
SalePrice,saledate,LegalReference  order by UniqueID) as rownum
from housingdata)
delete 
from rownumcte
where rownum>1;

if i use select instead of delete i am getting following output containing duplicates which is 104 rows

CodePudding user response：

Yes CTE are for many things very good, but for your purpose not.

Use instead a INNER JOIN.

CREATE TABLE housingdata (UniqueID int, 
ParcelID int
, PropertyAddress varchar(50)
, 
SalePrice DECIMAL(10,2)
,saledate Date
,LegalReference int)

INSERT INTO housingdata VALUES (1,1,'test',1.1, NOW(), 1),(2,1,'test',1.1, NOW(), 1)

delete hd
FROM housingdata hd INNER JOIN

(
select UniqueID ,row_number() over (partition by ParcelID, PropertyAddress, 
SalePrice,saledate,LegalReference  order by UniqueID) as rownum
from housingdata) t1 ON hd.UniqueID = t1.UniqueID 
WHERE t1.rownum>1;

SELECT * FROM housingdata

UniqueID | ParcelID | PropertyAddress | SalePrice | saledate   | LegalReference
-------: | -------: | :-------------- | --------: | :--------- | -------------:
       1 |        1 | test            |      1.10 | 2022-02-25 |              1

db<>fiddle here

UPDATE

You could have used also the CTE as joined table

with rownumcte as (
select UniqueID ,row_number() over (partition by ParcelID, PropertyAddress, 
SalePrice,saledate,LegalReference  order by UniqueID) as rownum
from housingdata)
delete hd
from housingdata hd INNER JOIN rownumcte r ON hd.UniqueID = r.UniqueID
where rownum>1;

SELECT * FROM housingdata

UniqueID | ParcelID | PropertyAddress | SalePrice | saledate   | LegalReference
-------: | -------: | :-------------- | --------: | :--------- | -------------:
       1 |        1 | test            |      1.10 | 2022-02-25 |              1

db<>fiddle here