Delete Dups with CTE or Derived Table






 if object_Id('Names') is not null drop table Names

Create table Names (
    firstname varchar(200),
    lastName varchar(200)
    
)
insert into Names (firstname, lastName) values ('John', 'Smith')
insert into Names (firstname, lastName) values ('John', 'Smith')
insert into Names (firstname, lastName) values ('John', 'Smith')
insert into Names (firstname, lastName) values ('Terry', 'witherspoon')
insert into Names (firstname, lastName) values ('Terry', 'witherspoon')
insert into Names (firstname, lastName) values ('Terry', 'witherspoon')

select * from Names


--with CTE
 
;with mycte as (Select lastName,  firstName,  row_Number() over (Partition by  lastname, FirstName order by lastname, FirstName) as RowNum From Names
)
Delete from mycte
where RowNum >1

--Or with a derived table

 DELETE t 
 FROM
	(
	select lastName,  firstName
	,  row_Number() over (Partition by  lastname, FirstName order by lastname, FirstName) as rn From Names) t  
	WHERE t.rn > 1 ;
 
 
 
select * from Names

GO



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s