Deleting duplicate rows - 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

SQL Server Development (2000)

Deleting duplicate rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-22 : 17:09:26

Subu writes "Hi

I have a table in sql and looks like this.

I DName Education phone fax Department
1 SS MS 123 456 CSE
1 SS MS 123 456 ACC
2 QQ BS 665 665 CSE
3 AS BE 987 111 BME
3 AS BE 987 111 IPA

Basically the records are same except Department which differs for the same person. I would like to delete duplicate phone and fax numbers . Example ID 1 shall have two records but only the phone anf fax are to be deleted from the second row. ID 2 is fine and doesnt need any modification bcos it has only one department.

The desired result is below.

ID Name Education phone fax Department
1 SS MS 123 456 CSE
1 SS MS ACC
2 QQ BS 665 665 CSE
3 AS BE 987 111 BME
3 AS BE IPA

Please help."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-22 : 17:51:39

Try this:



UPDATE t1
SET	phone = '', 
	fax = ''
FROM Table1 t1
INNER JOIN
(
	SELECT [ID], DName, Education, MIN(Department) AS Department
	FROM Table1
	GROUP BY ID, DName, Education
) t2
ON t1.[ID] = t2.[ID] AND t1.DName = t2.DName AND 
   t1.Education = t2.Education AND t1.Department = t2.Department

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources