1

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512))..

My amateur-ish SQLfoo lead me to this non-functioning query (for testing reasons I just selected the duplicates)

SELECT num, com FROM TableA
WHERE num, com IN
    (
        SELECT count(*) AS co FROM TableA WHERE co > 1 GROUP BY num, com
    )

How can I delete duplicates from TableA identified by num and com?

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

edit

from the suggestions I've tried the following two queries. TableA features ~220 entries, where ~80 are duplicates. first @dimitars suggestion

select ROW_NUMBER() OVER (PARTITION BY [num], com ORDER BY [num], com) as 'RowNumber', t1.*
    into ##TableA
    from TableA t1  

delete t1
--select t1.* 
from TableA  t1
join ##TableC t2
    on t2.[num]=t1.[num] and t2.com=t2.com
where t2.RowNumber > 1

resulting in every entry being deleted. And @marcelo miorellis suggestion

select * from TableA;
with cte as (
    select *, row_number() over (partition by [num], com order by [num], com ) as Picker
    from TableA
    )
select cte 
where Picker > 1

which gives me

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Picker'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'cte'.
3
  • @marcelo's query has ...delete cte where.... You have ...select cte where... Nov 24 '14 at 13:57
  • @ypercube.. correct, I first wanted to select 'em before deleteing them.. I just tried using the same with delete and it works as intended! thank you Nov 24 '14 at 14:01
  • 2
    @DaedalusMythos cte in the example is like a table. you need to write "select * from cte where Picker > 1" and not "select cte where Picker > 1" Nov 24 '14 at 14:41
4
--=======================================================
-- delete the duplicate records from table @t
-- keeping a single unit of each
-- marcelo miorelli 24-nov-2014
--=======================================================


--=======================================================
--create a table variable and insert records in it
-- just for this example
--=======================================================
declare @t table ([num] int, com varbinary(512) )

insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 3, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )

--=======================================================
-- this is going to show the table before the deletes
--=======================================================
select * from @t


--=======================================================
-- this deletes the duplicates - leaving only 1 of each
--=======================================================
; with cte as (
    select *
        , row_number() over (partition by [num],com  order by [num],com ) as Picker
    from @t
    )
delete cte 
where Picker > 1


--=======================================================
-- this shows the table after the deletion - without the deletes
--=======================================================
select * from @t
3
  • 3
    The semi-colon ; should really go at the end of select * from @t; rather than before the with. Get used to terminating each statement with a semi-colon. sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/… Nov 24 '14 at 10:37
  • thanks for this extensive answer.. but I'm unable to execute the query.. Msg 207, Level 16, State 1, Line 9 Invalid column name 'Picker'. Msg 207, Level 16, State 1, Line 8 Invalid column name 'cte'. Nov 24 '14 at 10:44
  • @DaedalusMythos it works fine for me in sql2016 and sql 2014, I don't see why it shouldn't work for you. are you sure you have just copied and pasted the script? what version of sql server? Nov 27 '17 at 10:18
2
select ROW_NUMBER() OVER (PARTITION BY num, com  ORDER BY identity_field) as 'RowNumber', t1.*
    into ##TableA
    from TableA t1  

--delete t1
select t1.* 
from TableA t1
join ##TableA t2
    on t2.identity_field=t1.identity_field
where t2.RowNumber > 1

but first make backup of your table

9
  • thank you for your suggestion. I assume identity_field is supposed to be the column that serves as a unique ID for the entries. I do not have such an identifier.. Nov 24 '14 at 10:57
  • 1
    then use 'num' and 'com' or some field that is unique(if you have one)
    – dimitar
    Nov 24 '14 at 11:19
  • TableA has ~200 entries, but selecting the ones that should be deleted (as you do in your answer) gives me 3k entries.. I'm confused. Nov 24 '14 at 13:09
  • either I'm doing something wrong or this doesn't really work.. I've executed the query above, with the delete and all have been deleted. But from ~200 entries 80 are duplicates.. Nov 24 '14 at 13:24
  • 1
    try this: select * from TableA; with cte (num,com,Picker)as ( select *, row_number() over (partition by [num], com order by [num], com ) as Picker from TableA ) select * from cte where Picker > 1 and check here technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
    – dimitar
    Nov 24 '14 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.