Solved

Update sql statement for DELETING DUPS in 2 fields

Posted on 2011-09-26
2
208 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Managing Columnstore Indexes 2 32
Extract string portion 2 24
Replace Dates in query 14 42
Find data in a column which is not in  a date format 29 31
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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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