none
Delete duplicate records from sql 2000 table except newest

    Question

  • Hi,

    I need to ba able to delete the old duplicats from sql 2000 based by date

    i did look on the web and found some solutions for 2005 not sql 2000.

    thanks

    Tuesday, June 14, 2011 11:23 AM

Answers

  • Are you sure? See full repro, (in your case we need some unique column so I added an IDENTITY)

    CREATE TABLE dbo.Table_1

    (

    invoice nvarchar(50) NULL,

    imei nvarchar(50) NULL,

    date datetime NULL,

    type nvarchar(50) NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO dbo.Table_1  

    SELECT 'C002321','0065141030202','2011-05-03 00:00:00.000','CR'

    UNION ALL

    SELECT 'I028472','0065141030202','2011-05-10 00:00:00.000','IN'

    UNION ALL

    SELECT 'I028472','0065141030202','2011-05-03 00:00:00.000','IN'

     

    ALTER TABLE dbo.Table_1 ADD id INT NOT NULL IDENTITY(1,1)

    GO

    DELETE FROM dbo.Table_1 

     

    WHERE EXISTS 

     

    (SELECT * FROM dbo.Table_1  TWD

     

    WHERE TWD.id < dbo.Table_1.id 

     

    AND TWD.invoice = dbo.Table_1 .invoice

     

    AND TWD.imei = dbo.Table_1.imei )

     

     

     

    SELECT * FROM Table_1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by deti Wednesday, June 15, 2011 2:49 PM
    Wednesday, June 15, 2011 9:16 AM
    Answerer
  • In the above sample, do you want to keep CR record and the newest IN record (delete the third record)?

    If yes, then try:

     

    delete T from Table_1 T inner join (select Max([Date]) as MaxDate, Imei, [Type] from Table_1 GROUP BY Imei, [Type]) X 
    
    ON T.Imei = X.Imei and T.[Type] = X.[Type]
    
    where T.[Date]< X.MaxDate
    

    If you only want to keep the middle record regardless of the Invoice Type, then:

     

    delete T from Table_1 T inner join (select Max([Date]) as MaxDate, Imei from Table_1 GROUP BY Imei) X 
    
    ON T.Imei = X.Imei 
    
    where T.[Date]< X.MaxDate

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by deti Wednesday, June 15, 2011 2:49 PM
    Wednesday, June 15, 2011 12:44 PM
    Moderator

All replies

  • http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, June 14, 2011 11:24 AM
    Answerer
  • Hello Deti,

    You can check the following resource http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

    There there is an example using SET ROWCOUNT, and an other method using a cursor with TOP clause, etc.

    Tuesday, June 14, 2011 11:40 AM
    Moderator
  • I hope this will work in SQL Server 2000;

    Create Table #tbl (id int, dt Datetime,id2 int)
    
    Insert into #tbl
       Select 1,'2011-01-01',1 union all
       Select 1,'2011-01-02',2 union all
       Select 1,'2011-01-03',3 union all
       Select 2,'2011-01-02',4 union all
       Select 2,'2011-01-03',5 union all
       Select 2,'2011-01-04',6 union all
       Select 2,'2011-01-05',7 union all
       Select 3,'2011-01-05',8 union all
       Select 3,'2011-01-06',9 union all
       Select 4,'2011-01-04',10 union all
       Select 4,'2011-01-05',11 union all
       Select 4,'2011-01-06',12 union all
       Select 4,'2011-01-07',13
    
    
    Delete from #tbl where id2 not in (
    Select Distinct c.id2
    from #tbl a
    Cross Apply (Select Top 1 id2,dt from #tbl where id = a.ID order by dt desc) c
    )
    
    Select * from #tbl
    
    Drop table #tbl
    
    

     


    Please visit my Blog for some easy and often used t-sql scripts
    Tuesday, June 14, 2011 11:53 AM
  • Hi

     

    Try this variation.

     

    Create table tblDates(ID int identity(1,1),AppDate Datetime)


    Insert into tblDates(AppDate)

    Values ('2011-01-01'),

    ('2011-01-01'),

    ('2011-01-01'),

    ('2011-01-02'),

    ('2011-01-02'),

    ('2011-01-03'),

    ('2011-01-03'),

    ('2011-01-04'),

    ('2011-01-05')

    delete
    tblDates  
    where tblDates.Id not in ( Select Min(ID) From tblDates B where tblDates.AppDate = B.AppDate)


     
    Note: Depending upon the requirement you can change the underlined part. Either Min or Max. If you want to keep the records that were inserted last use Max otherwise use Min.

     

    Thanks & Regards



    Abdul Azeem
    Tuesday, June 14, 2011 1:28 PM
  • Atif,

    CROSS APPLY was introduced in 2005.

    For OP:

    Can you please post your table structure and some samples?

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, June 14, 2011 1:33 PM
    Moderator
  • Solution is available on "Common Solutions for T-SQL" on MSDN. Try looking at below link

    http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows&referringTitle=Home

    Some other Common SQL Examples are available at : http://archive.msdn.microsoft.com/SQLExamples

     

    • Proposed as answer by abhi035 Tuesday, June 14, 2011 2:18 PM
    Tuesday, June 14, 2011 2:18 PM
  • hi naomi,

    sample data:

    invoice          imei                              date                           type

     

    C002321	0065141030202		2011-05-03 00:00:00.000 	CR
    I028472	0065141030202   	2011-05-10 00:00:00.000	 IN
    I028423	0065141030202		2011-05-03 00:00:00.000	 IN
    

    table
    CREATE TABLE dbo.Table_1
    	(
    	invoice nvarchar(50) NULL,
    	imei nvarchar(50) NULL,
    	date datetime NULL,
    	type nvarchar(50) NULL
    	) ON [PRIMARY]
    GO
    

     



    Wednesday, June 15, 2011 8:34 AM
  • Why  you did not read my link I posted yesterday?

    ALTER TABLE dbo.Table_1 ADD id INT NOT NULL IDENTITY(1,1)

    DELETE FROM dbo.Table_1 

    WHERE EXISTS 

    (SELECT * FROM dbo.Table_1  TWD

    WHERE TWD.id < dbo.Table_1.id 

    AND TWD.invoice = dbo.Table_1 .invoice

    AND TWD.imei = dbo.Table_1.imei )

     

    SELECT * FROM Table_1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 15, 2011 8:55 AM
    Answerer
  • hi uri,

    i did read your post but i had no id's on the table ( i thought th id was the primary key)

    i'll give it a try and let you know


    URI i get this (0 row(s) affected) when i run your sugestion ?

     

    Wednesday, June 15, 2011 9:04 AM
  • Are you sure? See full repro, (in your case we need some unique column so I added an IDENTITY)

    CREATE TABLE dbo.Table_1

    (

    invoice nvarchar(50) NULL,

    imei nvarchar(50) NULL,

    date datetime NULL,

    type nvarchar(50) NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO dbo.Table_1  

    SELECT 'C002321','0065141030202','2011-05-03 00:00:00.000','CR'

    UNION ALL

    SELECT 'I028472','0065141030202','2011-05-10 00:00:00.000','IN'

    UNION ALL

    SELECT 'I028472','0065141030202','2011-05-03 00:00:00.000','IN'

     

    ALTER TABLE dbo.Table_1 ADD id INT NOT NULL IDENTITY(1,1)

    GO

    DELETE FROM dbo.Table_1 

     

    WHERE EXISTS 

     

    (SELECT * FROM dbo.Table_1  TWD

     

    WHERE TWD.id < dbo.Table_1.id 

     

    AND TWD.invoice = dbo.Table_1 .invoice

     

    AND TWD.imei = dbo.Table_1.imei )

     

     

     

    SELECT * FROM Table_1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by deti Wednesday, June 15, 2011 2:49 PM
    Wednesday, June 15, 2011 9:16 AM
    Answerer
  • As Uri said, we need to have some unique column to identify each row uniquely, you can add an Idendity field and delete your duplicate records then drop that identity field.
    Thanks & Regards Prasad DVR
    Wednesday, June 15, 2011 9:32 AM
  • URI The Invoice  is not the same just the IMEI is the same,

     

    Wednesday, June 15, 2011 11:39 AM
  • Did it return desired result? Change the query for your needs.
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 15, 2011 11:43 AM
    Answerer
  • In the above sample, do you want to keep CR record and the newest IN record (delete the third record)?

    If yes, then try:

     

    delete T from Table_1 T inner join (select Max([Date]) as MaxDate, Imei, [Type] from Table_1 GROUP BY Imei, [Type]) X 
    
    ON T.Imei = X.Imei and T.[Type] = X.[Type]
    
    where T.[Date]< X.MaxDate
    

    If you only want to keep the middle record regardless of the Invoice Type, then:

     

    delete T from Table_1 T inner join (select Max([Date]) as MaxDate, Imei from Table_1 GROUP BY Imei) X 
    
    ON T.Imei = X.Imei 
    
    where T.[Date]< X.MaxDate

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by deti Wednesday, June 15, 2011 2:49 PM
    Wednesday, June 15, 2011 12:44 PM
    Moderator
  • Thank you all for yor help
    Wednesday, June 15, 2011 2:51 PM