I have SQL Server table that has col1, col2, col3, col4, col5, col6, col7, col8, col9, col10.
I want delete the duplicate based on col1, col2, col3.
The row that should be deleted is where col6=0 and col7=0 and col8=0.
We can use a deletable CTE here:
WITH cte AS (
SELECT *, COUNT(*) OVER (PARTITION BY col1, col2, col3) cnt
FROM yourTable
)
DELETE
FROM cte
WHERE cnt > 1 AND col6 = 0 AND col7 = 0 AND col8 = 0;
The CTE above identifies "duplicates" according to your definition, which is 2 or more records having the same values for col1
, col2
, and col3
. Then we delete duplicates meeting the requirements on the other 3 columns.