Navigation

	Home Page
	Index
	All Pages

Quick Search

Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Discuss (0) History

Deleting Duplicates without a Primary Key - SQL Server

RSS

Modified on Thu, May 01, 2014, 5:35 PM by Administrator Categorized as SQL Server

Overview¶

This article will explain how to delete duplicate rows from a table that has no primary key

Setup¶

if object_id('dbo.Person') is not null drop table dbo.Person
go
create table dbo.Person (
     FirstName varchar(50) not null
    ,LastName varchar(50) not null
)

insert into dbo.Person (FirstName, LastName) values ('Patrick','Henry'),('Patrick','Henry'),('George','Washington'),
('Thomas','Jefferson'),('John','Hancock') ,('Thomas','Jefferson')

select * from dbo.Person

Deleting Duplicates¶

;with cte as (
    select 
         FirstName
        ,LastName
        ,RowNum = row_number() over (partition by FirstName, LastName order by FirstName)
    from 
        dbo.Person
    )
delete cte 
where  RowNum > 1

select * from dbo.Person