Jasinski Technical Wiki

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

PoweredBy

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.