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