Twisted dup tails - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Twisted dup tails

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-13 : 15:14:08

Here's an abstract. A table with no createDate or Key column has 3 columns: ColA, ColB, ColC.

there are duplicates (argh!)

SELECT ColA, ColB, ColC, Count(*) As Dups
FROM MyTable
GROUP BY ColA, ColB, ColC
HAVING Count(*) > 1

Is there a way to delete the dups, (leaving 1) without a loop? How about with a loop?

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-13 : 15:14:43

And I didn't write this table.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 15:25:57

Sam,

The whole row is the key based on your description

SELECT DISTINCT ColA, ColB, ColC INTO myNewTable99 FROM myTable

Rename the old table to myTable_backup

Rename myNewTable99 to myTable

Done

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-13 : 15:37:07

one more step: create a unique constraint on all 3 columns to avoid duplicates in the future !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources