Solved

Delete duplicates from table and leave one record with the latest date_modified

Posted on 2006-06-16
2
273 Views
Last Modified: 2008-02-01
I have a table with lots of duplicates. I need to delete them based on [record id#]. I use the code below for this.
I have a few columns in the table. My previous procedure instead of updating records, appended new records to the table creating duplicates. I have a column [Date Modified] and I need to delete all duplicates (record id should be unique) but leave one record with the latest [Date Modified] (i.e. I have 50 records with the same record id but different [Date Modified]. I need only one record for this Record ID but it should have the latest [Date Modified].
I would really appreciate your advice.

Code to delete records without checking for the latest [Date Modified]

DECLARE @id INT

DECLARE dupsCsr CURSOR READ_ONLY FOR
SELECT [Record Id#], COUNT(*) AS numDups
FROM [infoteam].[Invoice_Recon_Detail_Original]
GROUP BY [Record Id#]
HAVING COUNT(*) > 1
DECLARE @numDups INT

OPEN dupsCsr
FETCH NEXT FROM dupsCsr INTO @id, @numDups
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @numDups = @numDups - 1
     SET ROWCOUNT @numDups
     DELETE FROM [infoteam].[Invoice_Recon_Detail_Original]
     WHERE [Record Id#] = @id
     FETCH NEXT FROM dupsCsr INTO @id, @numDups
END
CLOSE dupsCsr
DEALLOCATE dupsCsr
SET ROWCOUNT 0
0
Comment
Question by:InfoTeam
2 Comments
 
LVL 1

Expert Comment

by:asduth
Comment Utility
If there is only one record with the max time-stamp, then you can do the whole thing in a single delete statement, rather then using a cursor :
The basic idea is to do a sub-query which returns the latest records, and then delete from the main table all rows which do not join to this sub-query, hence :


delete [all]
from [infoteam].[Invoice_Recon_Detail_Original] [all]
left join (
      select [Record Id#], max([Date Modified]) [Date Modified]
      from [infoteam].[Invoice_Recon_Detail_Original]
      group by  [Record Id#]
) [latest]
on [all].[Record Id#] = [latest].[Record Id#] and [all].[Date Modified] = [latest].[Date Modified]
where [latest].[Record ID#] is NULL

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
just this 1 statement needed....

delete from [infoteam].[Invoice_Recon_Detail_Original]
  where exists (select x.[record id#] from [infoteam].[Invoice_Recon_Detail_Original] as x
               where x.[record id#] =[infoteam].[Invoice_Recon_Detail_Original].[record id#]
                  and x.[Date Modified] > [infoteam].[Invoice_Recon_Detail_Original].[date modified]
                        )


ie Delete the row if the same recordid exists with a higher modification date.


0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now