I have three columns in my table
KeyName, Number, Data
The data consist of lot of duplicate entries. I need to delete the 1 row from each of the duplicate entries in the table if the "KeyName" and "Data" are repeated. How shall I achieve this is SQL Query .
This should do it
WITH CTE
AS (SELECT KeyName
, Number
, Data
, [rn] = ROW_NUMBER() OVER(PARTITION BY KeyName
, Data ORDER BY KeyName
, Data)
FROM yourtable)
/* UNCOMMENT BELOW TO DELETE */
--DELETE
--FROM CTE
--WHERE rn > 1;
SELECT *
FROM CTE
WHERE rn > 1;
You can keep one row for each pair by using row_number():
with todelete as (
select t.*,
row_number() over (partition by keyname, data order by (select NULL)) as seqnum
from t
)
delete
from todelete
where seqnum > 1;
As with any such operation, you should check first before deleting. This is easy:
with todelete as (
select t.*,
row_number() over (partition by keyname, data order by (select NULL)) as seqnum
from t
)
select *
from todelete
where seqnum > 1;
