March 22, 2011 at 8:51 am
I've got table where I periodically need to delete duplicate records that have certain criteria
Fields
serial_number, name, scantime
we can have machine with duplicate serial number but having different name, and I would like to delete oldest record
ex.
serialnr: 111222333
name: test1
scantime: 3/1/2011
serialnr: 111222333
name: test2
scantime: 3/21/2011
Machine with scantime 3/1/2011 should be deleted
If there is large number of machines then I would like to delete them in bulks of 50-100 max, to avoid getting transaction log full issues.
Thank you
March 22, 2011 at 9:18 am
You can do that with a CTE and Row_Number pretty easily.
;with CTE as
(select row_number() over (partition by serialnr order by scantime desc) as Row
from dbo.MyTable)
delete top (100) from CTE where Row > 1;
You'll have to make it a real query of your real table, of course, but that principle should get you going in the right direction.
Why 50-100 at a time? I'm more used to chunks of 10,000 at a time or something like that, when limiting such a query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply