SQLTeam.com | Weblogs | Forums

Want to delete 42k duplicate rows from table which has 18 mill rows


#1

I have a table with 18 million rows and has 5 column combination primary key. column rev_center varchar(15), this one has same rev_center, one with 5 length and 15 length with all spaces.
rev_center '73000' i have same like this with spaces '73000 '

i want to delete those duplicates. which are close to 42k out of 18 million rows.

declare @GLNAME table (K_ACCT_NUMBER INT, REV_CENTER VARCHAR(15), SERVICE_DATE DATE, POSTING_DATE DATE, PDM VARCHAR(50))
insert @GLNAME
select 4543,'73000', '01/22/2012','01/24/2012','432' union all
select 4543,'73000 ', '01/22/2012','01/24/2012','432' union all ---this is duplicate can be deleted
select 3476,'55000', '04/14/2012','04/16/2012','634' union all
select 3476,'55000 ', '04/14/2012','04/16/2012','634' union all ---this is duplicate can be deleted
select 7666,'23450', '05/18/2012','05/18/2012','234'

select * from @GLNAME;

Thank you very much for the helpful info.


#2

DELETE FROM table_name
WHERE RIGHT(rev_center, 1) = SPACE(1)
--or rev_center LIKE '% '


#3

delete f from (select row_number() over (partition by keycol1, keycol2, keycol3, keycol4, keycol5 order by keycol1) AS rn from dbo.table1) AS f where rn >= 2


#4

FWIW we don't delete more than 10,000 rows in a single statement on a system that is "active" (during the night / scheduled downtime is fine ...) and frequently we set the bar much lower even than that.