?
Solved

Update sql statement for DELETING DUPS in 2 fields

Posted on 2011-09-26
2
Medium Priority
?
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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