We have a SQL Server Table from which has duplicate records. We want to keep only one records from duplicate records and delete all others.
We can use Common Table Expressions ( CTE) with Row_Number() function to achieve this
Let's create a SQL Server table with duplicate records
We can use Common Table Expressions ( CTE) with Row_Number() function to achieve this
Let's create a SQL Server table with duplicate records
USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer
VALUES(1,'Aamir','Shahzad',34) ,(1,'Aamir','Shahzad',34) ,(2,'Raza','M',32) ,(3,'Sukhjeet','Singh',28) ,(4,'Sukhjeet','Singh',28)
Write our CTE to Mark the duplicate records for Delete
;WITH CTE AS ( SELECT FirstName, LastName, Age, Row_number() OVER ( PARTITION BY FirstName, LastName,Age ORDER BY ( SELECT 1) ) AS Rn FROM dbo.Customer)SELECT * FROM CTEDelete Duplicate Records and keep only one copy;WITH CTE AS ( SELECT FirstName, LastName, Age, Row_number() OVER ( PARTITION BY FirstName, LastName,Age ORDER BY ( SELECT 1) ) AS Rn FROM dbo.Customer)DELETE FROM CTE WHERE Rn>1As we can see, all the duplicate copies are deleted and only unique records are left.
No comments:
Post a Comment