• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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