how to remove redundant records

Expand Post »

I have a table in MS SQL DB, the data was recorded with redundancy, is there an easy way to remove all but keep only one, for example:

(Toggle Plain Text)

id   name   age   dept
---------------------------
1    alan      20    A
2    william   23   B
2    william   23   B
3    mike      30   C
3    mike      30   C

I want to trim it to:

(Toggle Plain Text)

id   name   age   dept
---------------------------
1    alan      20    A
2    william   23   B
3    mike      30   C

what is the easy way to do that? DB running on MS SQL 2005.

Any idea?

michael123

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

93 posts
since Jun 2005

Permalink

Feb 14th, 2009

Re: how to remove redundant records

http://support.microsoft.com/kb/139444

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Feb 15th, 2009

Re: how to remove redundant records

There are few ways to do that, the simple one i think is by adding an index column, and than delete all records which has identical records with smaller index value:

MS SQL Syntax (Toggle Plain Text)
ALTER TABLE MyTable ADD i int IDENTITY;
 
DELETE FROM MyTable
WHERE i IN (
   SELECT T1.i 
   FROM MyTable AS T1
   INNER JOIN MyTable AS T2 ON T1.id=T2.id
   WHERE T1.i > T2.i
)
 
ALTER TABLE MyTable DROP COLUMN i;
ALTER TABLE MyTable ADD i int IDENTITY;

DELETE FROM MyTable
WHERE i IN (
   SELECT T1.i 
   FROM MyTable AS T1
   INNER JOIN MyTable AS T2 ON T1.id=T2.id
   WHERE T1.i > T2.i
)

ALTER TABLE MyTable DROP COLUMN i;

Doron Saar,

Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
www.nobhillsoft.com

Last edited by Doron_; Feb 15th, 2009 at 8:31 am. Reason: tags

Doron_

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: MS SQL 2005 Trigger : Transaction Scope and Parameters

Next Thread in MS SQL Forum Timeline: create update trigger with parameters mssql

DaniWeb Message

Cancel Changes

User Name
Password