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

Transact-SQL (2000)

Deleting Duplicate Rows

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2008-03-18 : 13:08:54

I accidentally imported rows into a table that already had identical rows. I'm trying to delete duplicate rows. The table looks like this:

Tbl1

ReportID---Reportdescription------ReportOwner
1----------1st Level Calls--------User1
2----------1st Level Calls--------User1
3----------2nd Level Calls--------User2
4----------2nd Level Calls--------User2
5----------Escalations------------User3

I'm trying to remove the rows using the "Reportdescription" column but I can't seem to figure out how.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-18 : 13:39:36

try this (with the select first to make sure you are only going to delete the rows you want)
if it works then you can swap the DELETE with the SELECT


select t.*
--delete	t
from	Tbl1 t
left outer join
	(select	min(ReportID) ReportID
		,Reportdescription
	from	Tbl1
	group by Reportdescription
	) d
		on d.Reportdescription = t.Reportdescription
		and d.Reportid = t.reportid
where	d.Reportdescription is NULL

EDIT:
forgot something important :)

Be One with the Optimizer
TG

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2008-03-18 : 13:47:11

Thanks much this worked perfect!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources