CTE the DELETE is not updateable

Question

I am new to SQL and using MYSQL Workbench. In this query, I am trying to find duplicate values and then delete the duplicates(anything > 1) But I am getting the error "The target table RowNumCTE of the DELETE is not updatable"

-- Remove duplicates
With RowNumCTE as(
select *, 
row_number() over (
partition by ParcelID,
    PropertyAddress,
SalePrice,
SaleDate,
LegalReference
order by UniqueID) row_num
from portfolioproject.nashvillehousing)
delete
from RowNumCTE
where row_num > 1;

Your guidance will be highly appreciated.

@Barmar In fact, on SQL Server the OP's delete would have worked. — Tim Biegeleisen, Jul 02 '21 at 04:05

score 0 · Answer 1 · answered Jul 02 '21 at 04:08

You can't delete from a CTE on MySQL 8+, as you might have been able to do on SQL Server. But, we can rephrase your logic as follows:

DELETE
FROM portfolioproject.nashvillehousing p1
WHERE EXISTS (SELECT 1 FROM portfolioproject.nashvillehousing p2
              WHERE p2.ParcelID = p1.ParcelID AND
                    p2.PropertyAddress = p1.PropertyAddress AND
                    p2.SalePrice = p1.SalePrice AND
                    p2.SaleDate = p1.SaleDate AND
                    p2.LegalReference = p1.LegalReference AND
                    p2.UniqueID < p1.UniqueID);

CTE the DELETE is not updateable

1 Answers1