Delete rows

[davidstevans]

Hi all,

I have a table: with the following columns

ID(AutoNumber) Serial Number(11 digit number)


In the Seriial number field there are a lot of values that are
duplicates What I would like is that every time I find a duplicate
Serial Number I would like to keep the 1st occurrence of that
particular Serial Number and completely delete the subsequent rows
of the same Serial Number.

thanks in advance for all your help

 

[John Spencer]

Warning BACKUP your data first

DELETE YourTable.*
FROM YourTable
WHERE YourTable.ID NOT IN
(SELECT Min(ID)
FROM YourTable
GROUP BY SerialNumber)

If you have a lot of records you may want to change the query to something
more complex.

DELETE YourTable.*
FROM YourTable
WHERE YourTable.ID IN
(SELECT ID
FROM YourTable as YT1 LEFT JOIN
(SELECT Min(ID)
FROM YourTable
GROUP BY SerialNumber) as YT2
ON YT1.ID = YT2.ID
WHERE YT2.ID is Null)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[davidstevans]

Warning BACKUP your data first

DELETE YourTable.*
FROM YourTable
WHERE YourTable.ID NOT IN
(SELECT Min(ID)
FROM YourTable
GROUP BY SerialNumber)

If you have a lot of records you may want to change the query to something
more complex.

DELETE YourTable.*
FROM YourTable
WHERE YourTable.ID IN
(SELECT ID
FROM YourTable as YT1 LEFT JOIN
(SELECT Min(ID)
FROM YourTable
GROUP BY SerialNumber) as YT2
ON YT1.ID = YT2.ID
WHERE YT2.ID is Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,

I ran the delete script and its prompting me for the value for
YT2.ID ?

 

[John Spencer]

Must have goofed somewhere. I think is was because I forgot to name the
result returned by the Min(Id). Try the following

DELETE YourTable.*
FROM YourTable
WHERE YourTable.ID IN
(SELECT ID
FROM YourTable as YT1 LEFT JOIN
(SELECT Min(ID) As Keep
FROM YourTable
GROUP BY SerialNumber) as YT2
ON YT1.ID = YT2.Keep
WHERE YT2.Keep is Null)


Otherwise, let's try this using a multistep approach.
First Query (save as YT2) - This identifies which records to KEEP.
SELECT Min(ID) as Keep
FROM YourTable
GROUP BY SerialNumber
Save that as YT2

Next query Identify which records to delete
SELECT ID
FROM YourTable LEFT JOIN YT2
ON YourTable.ID = YT2.Keep
WHERE YT2.Keep is Null
Save that as YT1

Now use the saved query in a delete query as
DELETE DistinctRow YourTable.*
FROM YourTable
WHERE ID IN (SELECT ID FROM YT1)





'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================