How to Trace and delete duplicate records

[jovillanuev]

Hi All,

I have my table with duplicate records. the duplicate records have the same ESN and Shipdate the difference is the time. there is no transaction in same date. That's why i would like to remove the other records. I need your help guys.

How to this.
1. I need to find all this records with the same ESN and the same Shipdate with different time.
2. How to delete this records and retain only 1 esn with the same shipdate
3. I would like to replace the time into 00:00:00 next time i upload the records into my history table.

Here is the sample:

MyTABLE
Create Table Mytable
(ESN varchar(35), Shipdate datetime)
Insert into Mytable (ESN, Shipdate) values ('268435457314195510','2011-02-18 19:35:34.263')
Insert into Mytable (ESN, Shipdate) values ('268435457314195510','2011-02-18 19:42:25.917')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-02-18 19:35:32.727')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-02-18 19:42:25.663')

Thank you in advance.

Jonel

[debasisdas]

since you have only 2 fields try the following.

1.

CREATE TABLE Mytable_New AS SELECT ESN, min(Shipdate) FROM Mytable
group by ESN, Shipdate;

2. Drop the table Mytable.
3. Rename Mytable_New to Mytable.

[jovillanuev]

I try already but this kind of script, it will not work.
because there's other transaction of esn from other month or year.

Create Table Mytable
(ESN varchar(35), Shipdate datetime)
Insert into Mytable (ESN, Shipdate) values ('268435457314195510','2011-02-18 19:35:34.263')
Insert into Mytable (ESN, Shipdate) values ('268435457314195510','2011-02-18 19:42:25.917')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-02-18 19:35:32.727')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-02-18 19:42:25.663')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-04-19 16:30:25.727')
Insert into Mytable (ESN, Shipdate) values ('268435457314207074','2011-04-19 16:42:28.663')

[BitBlt]

Here's a select statement you can try out (with an included commented-out delete that you can use if you like). Run it and check the results. The rows that show up with NULL in the second shipdate column are the ones that should be deleted.

select q.esn, CONVERT(varchar(12), q.shipdate, 101) as shdt, q.shipdate, r.shipdate 
--delete q
from dbo.mytable q
left join dbo.mytable r 
on q.esn = r.esn
and CONVERT(varchar(12), q.shipdate, 101) = CONVERT(varchar(12), r.shipdate, 101)
and q.shipdate < r.shipdate
--where r.shipdate is null

To make the delete work, comment out the line with 'select' and uncomment the line with 'delete' and the line with 'where'.

Here's the result set I got with the delete running against your test data from above:
ESN Shipdate
268435457314195510 2011-02-18 19:35:34.263
268435457314207074 2011-02-18 19:35:32.727
268435457314207074 2011-04-19 16:30:25.727
(sorry about the formatting)
Note that by using the CONVERT function it will effectively strip off the time component of your datetime column. When you load that data into a datetime, it will show the 00:00:000.000 you want.

Hope this works for you! Oh, and this was tested with SQL2008.

[Netcode]

First it shows your database is not well designed as you do nt have a unique identifier. Well, since you have a saving grace which is the time, you can delete all other records leaving the want you want by specifying the time in the WHERE clause