Delete duplicate records in my sql server

Asked Aug 08 '16 at 06:48

Active Jun 21 '18 at 07:19

Viewed 86 times

I am trying to delete duplicate or more than duplicate record in my table using this :

DECLARE db_cursor CURSOR FOR SELECT distinct LineNumber FROM SPMS.dbo.Lines
DECLARE @lineNumber VARCHAR(MAX);
DECLARE @lineId int;
DECLARE @count int;
   OPEN db_cursor;
FETCH NEXT 
   FROM db_cursor 
   INTO @lineNumber;
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
    SELECT @count=count(*) 
      FROM [SPMS].[dbo].Lines 
     WHERE @lineNumber=@lineNumber
           if @count>1
    BEGIN   
    SELECT @lineId=Id 
      FROM [SPMS].[dbo].Lines 
     WHERE @lineNumber=@lineNumber
    DELETE FROM [SPMS].[dbo].Lines 
     WHERE id=@lineId 
       END
FETCH NEXT 
      FROM db_cursor INTO @lineNumber;
       END;
     CLOSE db_cursor;
DEALLOCATE db_cursor;

But it delete all same records .why ?

edited Jun 21 '18 at 07:19

Prabhat Sinha

1,500
20
32

asked Aug 08 '16 at 06:48

Ehsan Akbar

6,977
19
96
180

3

You can do this using a simple CTE and a `DELETE` statement - no need to involve cursors, etc. See [this question](http://stackoverflow.com/q/18390574/15498) – Damien_The_Unbeliever Aug 08 '16 at 06:50
1

@Damien_The_Unbeliever is it ?WITH CTE AS( SELECT LineNumber, RN = ROW_NUMBER()OVER(PARTITION BY LineNumber ORDER BY LineNumber) FROM [SPMS].[dbo].[Lines] ) DELETE FROM CTE WHERE RN > 1 – Ehsan Akbar Aug 08 '16 at 06:55
@Damien_The_Unbeliever it works thank you .i just run the above query . – Ehsan Akbar Aug 08 '16 at 07:00
@Damien_The_Unbeliever you can answer the question . – Ehsan Akbar Aug 08 '16 at 07:00
@Damien_The_Unbeliever should i delete the question ? – Ehsan Akbar Aug 08 '16 at 07:03
1

I think it's fine to exist as a duplicate. – Damien_The_Unbeliever Aug 08 '16 at 07:07
@Damien_The_Unbeliever ok thank you – Ehsan Akbar Aug 08 '16 at 07:09

Delete duplicate records in my sql server

0 Answers0