Update sql statement for DELETING DUPS in 2 fields

sql server 2008
What I need:
To delete duplicates based on 2 fields.


1 table:
In my Table...
field 1  =  "Material_no"  is a PK field(Yes dups ok).
field2 =   "alt_acc_material_no" (pk field no dups)
Right now I'm using cte to delete dups from a table with 1 primary field...

code for one primary field:
with CTE as (
      select *, row_number() over (partition by material_no order by material_no) rn
      from crs_staging
delete from CTE where rn > 1

1A123       4RRT5     < OK
1A123       55T56     <  OK
1SS23       1WW25  
5TT56        666T6      
5TT56        666T6            <----- DELETE


Who is Participating?
venk_rConnect With a Mentor Commented:
try this
with CTE as (
      select *, row_number() over (partition by material_no,alt_acc_material_no order by material_no,alt_acc_material_no) rn
      from crs_staging
delete from CTE where rn != 1

FordraidersAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.