[AccessD] deleting duplicates

This will keep the first instance of  a record, and delete all others
afterward (basically coded, to do what you did by pushing them into a new
table adn not allowing duplicates)

You can change the MIN to a MAX to keep the last record and delete all of
the earlier entries.

David


DELETE tblYourTable
WHERE PKID IN
(SELECT A.PKID FROM
    (SELECT PKID FROM tblYourTable WHERE SomeField IN (SELECT SomeField FROM
tblYourTable GROUP BY SomeField HAVING COUNT(SomeField)>1)) A
    LEFT JOIN
        (
            SELECT MIN(PKID) AS MinPKID, SomeField
            FROM tblYourTable WITH (NOLOCK)
            WHERE SomeField IN
                (SELECT SomeField
                 FROM tblYourTable WITH (NOLOCK)
                 GROUP BY SomeField
                 HAVING COUNT(SomeField)>1
                )
            GROUP BY SomeField
        ) B
    ON A.PKID = B.MinPKID
    WHERE B.MinPKID IS NULL
    )




On Wed, Jun 24, 2009 at 9:48 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Hi All:
>
> It is easy to list duplicates but what is needed to do is to list all
> duplicates greater than one, with only sql.
>
> Possible?
>
> Jim
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>