• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

how to delete duplicate records from table in sqlserver2005

Hi
i have a below table.
named emp
Empid  Empname  sal       city

1            sai           100      pune

2            ram           400     hyd

3            ram          500       delhi

2            ram          500        hyd

i want to write a query to remove the duplicate redcords from the above tables.
Ravi
0
mu_ravi1
Asked:
mu_ravi1
  • 3
  • 3
  • 2
  • +4
3 Solutions
 
Pratima PharandeCommented:
there is no duplicate reprd in your example

refer this to remove duplicate

http://support.microsoft.com/kb/139444
0
 
Lee SavidgeCommented:
What is your criteria for determining whether a record is a duplicate or not?

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mu_ravi1Author Commented:
Hi
i have a below table.
named emp
Empid  Empname  sal       city

1            sai           100      pune

2            ram           400     hyd

3            ram          500       delhi

2            ram         400        hyd

i want to write a query to remove the duplicate redcords from the above tables.
Ravi
0
 
mayank_joshiCommented:
try this:-

with cte as
( select Empid,Empname, sal ,city,ROW_NUMBER() over (order by Empid) as rowid from emp
)
delete from cte where rowid>1

Open in new window

0
 
mayank_joshiCommented:
sorry it got typed incorrectly.try this:-

with cte as
( select Empid,Empname, sal ,city,ROW_NUMBER() over (order by Empid) as rowid from emp
)
delete from cte where rowid>1

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
delete a
from dbo.tem a, dbo.tem b
where a.patientidSource = b.patientidSource
and a.indent > b. indent
0
 
Lee SavidgeCommented:
And you still haven't told us what your criteria is for determining a duplicate record.

Are you looking for duplicates based on empname or empid and empname or empid and empname and empcity?
0
 
Vipul Patel.NET ExpertCommented:
Syntax:
;WITH Cte AS
(
   SELECT ROW_NUMBER() over (order by [Field1],[Field2],......[FieldN]) AS Sequence FROM [TableName]
)
DELETE FROM Cte WHERE Sequence >1

Query:
;WITH Cte AS
(
   SELECT ROW_NUMBER() over (order by Empid,Empname) AS Sequence FROM emp
)
DELETE FROM Cte WHERE Sequence >1
0
 
GhunaimaCommented:
Try this    
select
empid, empname, max(sal) sal, city
from emp group by empid, empname, city
0
 
mayank_joshiCommented:
try this:-
with cte as
( select Empid,Empname, sal ,city,ROW_NUMBER() over (  Partition BY  Empid,Empname order by Empid  ) as rowid from emp
)
delete from cte where rowid>1

Open in new window

0
 
mu_ravi1Author Commented:
good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now