0

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:

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:

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?

3
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by Doron_
0

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:

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.