Removing Duplicate Records Using Correlated SubQuery In SQL Server

In this article I will demonstrate how to remove duplicate records in SQL Server.
  • 7625

In this article I will demonstrate how to remove duplicate records in SQL Server. In a correlated subquery, the first outer query is executed, this result is used by an inner sub-query for its execution, then  result is again used by outer query to get final result.

Create Table

CREATE TABLE [dbo].[Student1Details](

      [ID] [int] NOT NULL,

      [Name] [varchar](50) NULL,

      [Branch] [varchar](10) NULL,

      [Location] [varchar](10) NULL,

 CONSTRAINT [PK_Student1] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)

) ON [PRIMARY]

Insert some values

INSERT INTO Student1Details

SELECT 1, 'Nitin', 'CS','IND' UNION ALL

SELECT 2, 'Ravi', 'EI','ENG' UNION ALL

SELECT 3, 'Tim', 'ME','US' UNION ALL

SELECT 4, 'Rick', 'ME','IND' UNION ALL

SELECT 5, 'Rakesh', 'CS','ABD' UNION ALL

SELECT 6, 'Tarun', 'ME','IND' UNION ALL

SELECT 7,'Raushan','IT','IND' UNION ALL

SELECT 8,'Aman','EC','US'

SELECT * FROM Student1Details

Student1Details Table

StudentTable.jpg

Removing Duplicate Records

select * from Student1Details S1

where S1.ID=(select max(ID) from Student1Details S2

where S1.Location=S2.Location)

GO

--Deleting Duplicate Records

delete Student1Details where ID< (select max(ID) from Student1Details S2

where S2.Location = Student1Details.Location)

GO

SELECT * FROM Student1Details

GO

Output:

 StudentTable1.jpg

© 2019 DotNetHeaven. All rights reserved.