Remove duplicates records and add unique key to the column

• Bharat21

  Right there with Babe

  Points: 781

  More actions

  April 22, 2017 at 1:38 am

  Go to Answer

  #329907

  I have to  delete  duplicates records  which having  same date and same ID with below conditions and then  add unique key on date and ID 

  SELECT SDATE , ID , COUNT(1) AS CNT FROM  OT_DTL GROUP BY SDATE , ID HAVING COUNT(1)>1

  I have tried with many different ways  and google but it gives error for  delete  the  records 
  Kindly suggest on this if any one knows   or  available asap

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  April 22, 2017 at 10:21 am

  Answer

  #1939429

  
WITH OrderedOT_DTL
AS
(
    SELECT *
        -- You may have a column value you prefer to keep. (SELECT NULL) just orders as read.
        ,ROW_NUMBER() OVER (PARTITION BY ID, SDATE ORDER BY (SELECT NULL)) AS rn
    FROM OT_DTL
)
DELETE OrderedOT_DTL
WHERE rn > 1;
GO
ALTER TABLE OT_DTL
ADD CONSTRAINT UQ_OT_DTL_ID_SDATE UNIQUE (ID, SDATE);
GO


• Bharat21

  Right there with Babe

  Points: 781

  More actions

  April 24, 2017 at 12:57 am

  #1939487

  Thank you so much, this code is working properly .