?
Solved

Update sql statement for DELETING DUPS in 2 fields

Posted on 2011-09-26
2
Medium Priority
?
219 Views
Last Modified: 2012-05-12
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



Thanks
fordraiders


0
Comment
Question by:Fordraiders
2 Comments
 
LVL 8

Accepted Solution

by:
venk_r earned 2000 total points
ID: 36602075
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

0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 36817883
Thanjks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question