How to delete duplicate record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to delete duplicate record?

Author

Topic

tskmjk
Starting Member

11 Posts

Posted - 2010-01-22 : 08:40:32

I have a requirement in Microsoft access where excel sheet is uploaded, a query should be written to weed out duplicates and then the duplicate free data should be exported. I have trouble writing sql query. My data and query are as follows:

Sno Sname
-----------------
1 suresh
2 kumar
3 raj
4 kumar

I want to remove one row (4) from the above table. I wrote the following query to identify the duplicates:

SELECT [Sno]
FROM tab1
WHERE [Sname] In (SELECT [Sname] FROM [tab1] As Tmp GROUP BY [Sname] HAVING Count(*)>1 )
ORDER BY [Sname]);

To delete the records, I am writing the query:

DELETE *
FROM tab1
WHERE sno in (SELECT [Sno]
FROM tab1
WHERE [Sname] In (SELECT [Sname] FROM [tab1] As Tmp GROUP BY [Sname] HAVING Count(*)>1 )
ORDER BY [Sname]);

But the above query is deleted both the records. I want one record to be intact.

kindly help me ASAP

Regards,

T.Suresh Kumar

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-22 : 09:18:30

[code]with A as
(
select
Sno,
row_number() over (partition by Sname order by Sno) as N
from
tab1
)
delete from #tmp
from #tmp t inner join A on a.Sno = t.Sno and N > 1[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 09:18:45

Just to make it more clear which commands can be used...or not.
Is it SQL Server 2005 or is it MS Access where your query is running?

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-22 : 09:20:58

quote:
Originally posted by webfred

Just to make it more clear which commands can be used...or not.
Is it SQL Server 2005 or is it MS Access where your query is running?

No, you're never too old to Yak'n'Roll if you're too young to die.

Listen to webfred :) I have the bad habit of shooting before asking questions. He is more careful and deliberate.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 09:33:15

Maybe it is SQL Server 2005, then your solution would work...but there is a little typo
... delete from ~~#tmp~~
from #tmp t inner join ...

Sorry - I don't wanna be like a wisenheimer

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources