none
Need a help in a SQL Query - Loop

    Question

  • I am assigned to delete duplicate records exists in a very old database.

    I have used the following code to find out the duplicate record.

    --------------------------------------------------------------------------------------------------

    SELECT CardNumber,AttendanceDate, Count(*) Total FROM Attendance Group By CardNumber, AttendanceDate HAVING Count (*) > 1 ORDER BY CardNumber DESC;

    --------------------------------------------------------------------------------------------------

    The above snippet will give me the count of duplicate records in the database.

    Now, my question is how can I loop through the above statement and delete the duplicate records based on the condition specified below:

    DELETE FROM Attendance WHERE CardNumber='0137' AND AttendanceDate='2013-10-06' AND Days=0;

    Here the CardNumber and AttendanceDate shall be replaced by using the records retrieved from the SELECT statement.



    Wednesday, November 20, 2013 2:58 PM

Answers

  • Thank you Kalman Toth for a quick reply.

    I made it through CURSOR.

    Thank you all.

    • Marked as answer by Ranjith K.U. _ Wednesday, November 20, 2013 3:54 PM
    Wednesday, November 20, 2013 3:54 PM
  • Thank you David.

    I used CURSOR and solved the issue.  The code that I used as follows:

    DECLARE @Cardnumber VARCHAR(50)

    DECLARE @AttendanceDate smalldatetime

    DECLARE @Fetchinto Bigint

    DECLARE db_cursor CURSOR FOR

    SELECT CardNumber, AttendanceDate, Count(*) TotalCount FROM ATTENDANCE

    GROUP BY CardNumber, AttendanceDate HAVING Count(*) > 1

    ORDER BY CardNumber DESC

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @Cardnumber, @AttendanceDate, @Fetchinto

    WHILE @@FETCH_STATUS=0

    BEGIN

    DELETE FROM Attendance WHERE CardNumber=@CardNumber AND AttendanceDate=@AttendanceDate AND Days=0

    FETCH NEXT FROM db_cursor INTO @Cardnumber, @AttendanceDate, @Fetchinto

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    • Marked as answer by Ranjith K.U. _ Wednesday, November 20, 2013 4:15 PM
    Wednesday, November 20, 2013 4:13 PM

All replies

  • The following blog has methods to dedup tables:

    http://www.sqlusa.com/bestpractices/eliminateduplicates/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, November 20, 2013 3:07 PM
    Moderator
  • In my case the Primary Key is unique. Rest of the fields are identical.

    I need to delete those rows which are identical even though the Primary Keys are unique.


    Wednesday, November 20, 2013 3:11 PM
  • I think I should use CURSOR.

    Wednesday, November 20, 2013 3:20 PM
  • >I think I should use CURSOR.

    Not necessary.  Use a query like this from the link that Kalman posted.

    ;WITH CTE AS 
    (     
      SELECT RN=ROW_NUMBER() OVER 
         (
           PARTITION BY ProductID 
           ORDER BY ModifiedDate DESC 
         )     
      FROM Product
    )
    DELETE CTE 
    WHERE RN > 1
    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, November 20, 2013 3:53 PM
  • Thank you Kalman Toth for a quick reply.

    I made it through CURSOR.

    Thank you all.

    • Marked as answer by Ranjith K.U. _ Wednesday, November 20, 2013 3:54 PM
    Wednesday, November 20, 2013 3:54 PM
  • Thank you David.

    I used CURSOR and solved the issue.  The code that I used as follows:

    DECLARE @Cardnumber VARCHAR(50)

    DECLARE @AttendanceDate smalldatetime

    DECLARE @Fetchinto Bigint

    DECLARE db_cursor CURSOR FOR

    SELECT CardNumber, AttendanceDate, Count(*) TotalCount FROM ATTENDANCE

    GROUP BY CardNumber, AttendanceDate HAVING Count(*) > 1

    ORDER BY CardNumber DESC

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @Cardnumber, @AttendanceDate, @Fetchinto

    WHILE @@FETCH_STATUS=0

    BEGIN

    DELETE FROM Attendance WHERE CardNumber=@CardNumber AND AttendanceDate=@AttendanceDate AND Days=0

    FETCH NEXT FROM db_cursor INTO @Cardnumber, @AttendanceDate, @Fetchinto

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    • Marked as answer by Ranjith K.U. _ Wednesday, November 20, 2013 4:15 PM
    Wednesday, November 20, 2013 4:13 PM