Delete rows

  • Thread starter Thread starter davidstevans
  • Start date Start date
D

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
 
Hello davidstevens.
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
Click to expand...

DELETE *
FROM MyTable
WHERE Exists (SELECT * FROM MyTable as tmp WHERE
(tmp.[Serial Number]=MyTable.[Serial Number]) AND (tmp.ID<MyTable.ID));

also possible:

DELETE *
FROM MyTable
WHERE ID NOT IN (SELECT Min(ID) FROM MyTable GROUP BY [Serial Number]);
 
Back
Top