Finding and Deleting duplicate rows in table where multiple occurances of records is allowed

This question already has an answer here:

  • How can I remove duplicate rows?

    35 answers

  • TSQL Passing a varchar variable of column name to SUM function
  • Limit concurrent access to Asp.Net application
  • Save win user name by SQL server trigger
  • sql server 2008 security
  • SQL Server 2016 Upgrade Advisor Issue
  • MSSQL cast( to int) in SELECT gets executed before WHERE clause filters out bad values
  • 3 Solutions collect form web for “Finding and Deleting duplicate rows in table where multiple occurances of records is allowed”

    the below query should give you a list of all the duplicate booknumbers with the customerIDs, all you need to do is a simple delete statement with the results to remove the duplicate records

    SELECT count(bookNumber), booknumber, customerID FROM TableName GROUP BY booknumber, customerID having count(booknumber)> 1
    
    WITH CTE AS
    (
    SELECT  *, 
    ROW_NUMBER() OVER (PARTITION BY BookNumber, Customerid ORDER BY BookNumber) AS DUPS
    FROM Store.Books 
    )
    SELECT * FROM CTE WHERE DUPS > 1
    -- if you want to delete, replace last line with this:
    --DELETE FROM CTE WHERE DUPS > 1
    

    I should mention that order by booknumber desc wasn’t necessary so I removed the ‘desc’ part

    Brothers, This is one of the way to find out duplicates data, try it=)

    DECLARE @tempTable TABLE(
    CustomerID SMALLINT,
    BookLoan NVARCHAR(255),
    BookNumber INT,
    BookAuthor NVARCHAR(255)
    )
    
    INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'Clash Of Titans',23457,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'History of Soda',99899,'Brian Adams  ')
    
    Select *,Count(*) 'Occurrance' From @tempTable Group by 
    CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1
    
    
    Delete from @temptable
    where CustomerID = (Select customerID From @tempTable Group by
    CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
    AND BookLoan = (Select BookLoan From @tempTable Group by 
    CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
    AND BookNumber = (Select BookNumber From @tempTable Group by 
    CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
    AND BookAuthor = (Select BookAuthor From @tempTable Group by 
    CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
    
    Select * from @tempTable
    

    Or alternative way as below here

    DECLARE @tempTable TABLE(
    CustomerID SMALLINT,
    BookLoan NVARCHAR(255),
    BookNumber INT,
    BookAuthor NVARCHAR(255)
    )
    
    INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'Clash Of Titans',23457,'Dick VanDyke ')
    INSERT INTO @tempTable Values(112,'History of Soda',12345,'Brian Adams  ')
    
    ;WITH CTE AS
    (
    SELECT  *, 
    ROW_NUMBER() OVER (PARTITION BY CustomerID, BookLoan,BookNumber,BookAuthor 
    ORDER BY BookNumber) AS DUPS
    FROM @tempTable
    )
    DELETE FROM @tempTable
    WHERE CustomerID = (SELECT CustomerID FROM CTE WHERE DUPS > 1)
    AND BookLoan = (SELECT BookLoan FROM CTE WHERE DUPS > 1)
    AND BookNumber = (SELECT BookNumber FROM CTE WHERE DUPS > 1)
    AND BookAuthor = (SELECT BookAuthor FROM CTE WHERE DUPS > 1)
    
    SELECT * FROM @tempTable
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.