MS SQL

how to remove redundant records

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jun 2005

Posts: 92

Reputation: michael123 is an unknown quantity at this point

Solved Threads: 0

michael123 michael123 is offline

Offline

Junior Poster in Training

how to remove redundant records

Feb 13th, 2009

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:

Help with Code Tags

(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:

Help with Code Tags

(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?

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: how to remove redundant records

Feb 14th, 2009

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

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Feb 2009

Posts: 3

Reputation: Doron_ is an unknown quantity at this point

Solved Threads: 0

Doron_

Offline

Newbie Poster

Re: how to remove redundant records

Feb 15th, 2009

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:

 Help with Code Tags 
 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