Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

Deleting duplicate records in SQL

Experts,

Attached is the documents outlines the sample data and I want to delete the rows that are highlighted in red color. Have been trying with multiple scripts and not successful.

Thanks in advance
Document.docx
0
Tpaul_10
Asked:
Tpaul_10
  • 2
2 Solutions
 
rlarianCommented:
INSERT INTO tablenew
      SELECT * FROM table tT
      WHERE NOT EXISTS (SELECT * FROM tablenew T
                                    WHERE      T.[ItemNumber] = tT.[ItemNumber] AND
                                                T.[ItemCode] = tT.[ItemCode])

tablenew can be a temp table. then truncate table and insert tablenew back into table.
0
 
Tpaul_10Author Commented:
Didn't get it quite right with the above code rlarian, how about the following query and gets me what I wanted. Can you please review?

Delete ItemDetails where itemId not in (
select min(itemID) from ItemDetails A
group by ItemNumber,ItemCode) 

Open in new window


Thanks
0
 
rlarianCommented:
just tested. that'll work.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>  
;with del as (
   SELECT ItemNumber, ItemCode, ItemID
      row_number() OVER (partition by ItemNumber, ItemCode ORDER BY ItemID) as row_number 
   FROM your_table
)
DELETE FROM del WHERE row_number > 1

Open in new window

Nice timing; I just wrote an article titled SQL Server Delete Duplicate Rows Solutions, so if you have some time to kill give this a read and let me know if you like it.  The above is modeled after the 'Delete #1' section.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now