I have table and it has these columns:
and my table has 4 rows data same value as below
Subject 'Send Email for Notification'
My table name is
If I try this query:
*, ROW_NUMBER() OVER (ORDER BY pkID) RN
WHERE pkID = 44
DELETE FROM cte
WHERE RN > 1
I get this error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
I want to delete pkID equals to 44 it will delete 4 rows so how can I delete 3 rows pkID equals 44 ?
row_number to delete the duplicates
;with cte as ( select *,row_number() over(order by pkID) RN FROM yourtable where pkID = 44 ) delete from cte where RN>1
order by you can mention the in which
order you want to delete the
You can use a CTE and
ROW_NUMBER. It allows to change it easily to see which rows are deleted (or updated), therefore just change the
DELETE FROM CTE... to
SELECT * FROM CTE:
WITH CTE AS( SELECT e.*, RN = ROW_NUMBER() OVER (PARTITION BY pkID ORDER BY pkID) FROM dbo.EmailTemplates e WHERE pkID = @pkID -- if you want to delete only a specific ID ) DELETE FROM CTE WHERE RN > 1
If you remove
WHERE pkID = @pkID you will delete all duplicates by always keeping one row per
pkID. So you need to change
ORDER BY pkID to something different if you want to change the logic to something more meaningful, currently it's arbitrary.
If you want to take multiple columns into account, you just need to change
PARTITION BY pkID to (f.e.)
PARTITION BY pkID, Title.