How to delete off dupli data only keep 1 rec each

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 off dupli data only keep 1 rec each

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-06 : 21:31:35

Dear All,

May i know what is the sql statement that able to delete off duplication record & only keep 1 for each unique code?

Example:
DistCode DistID Sources
AU0004314 575790 AUS
AU0004314 575798 AUS
AU0004315 575806 AUS
AU0004315 575813 AUS
AU0004315 575817 AUS
AU0004316 575742 AUS
AU0004316 575750 AUS
AU0004317 575965 AUS
AU0004317 575972 AUS
AU0004318 575726 AUS
AU0004318 575734 AUS

I want to keep only one of the record AU0004314, AU0004315,AU0004316,AU0004317 ...etc

Please Advise.

Thank you.

Regards,
Micheale

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 22:07:01

http://weblogs.sqlteam.com/mladenp/archive/2009/01/05/The-simplest-way-to-delete-duplicates-and-compare-two-result.aspx

Make sure to read the comments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-06 : 22:45:32

Thank You tkizer.

It's work fine.

Regards,
Micheale

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-07 : 16:48:09

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 17:03:10

[code]DELETE FROM tableName
WHERE DistID NOT IN (
SELECT DistID
FROM (
SELECT DistCode, MIN(DistID) AS DistID
FROM tableName
GROUP BY DistCode) z)[/code]

There are 10 types of people in the world, those that understand binary, and those that don't.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-07 : 17:21:58

quote:
Originally posted by DBA in the making

DELETE FROM tableName
WHERE DistID NOT IN (
SELECT DistID
FROM (
SELECT DistCode MIN(DistID) AS DistID
FROM tableName
GROUP BY DistCode))

There are 10 types of people in the world, those that understand binary, and those that don't.

The problem is already solved.
And your provided solution will not work.

Edit: Because the derived table has no name and a comma is missing between DistCode and MIN(DistID)

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 17:33:42