Delete Duplicates

[Guest]

Is it possible to run a delete query to delete duplicates in a table? I have
one field in the table that is an order ID. The other field is an item
number. I'd like to build the query to identify an order ID and then delete
any duplicated item numbers for that order. Is this possible and if so, can
you give me an idea how to proceed? Thanks.

 

[Tom Ellison]

Dear Don:

The first issue in my mind is, if you have two rows with the same order ID,
which one is the duplicate?

Realizing that there would probably be other columns, and that the
information in these other columns may be different between the two rows, it
will make a lot of difference to the final result which one is deleted.

You will need some unique way of selecting which row is to be retained. The
SQL to do all this will be very involved in accomplishing that.

There are then two approaches. One is to delete the unwanted rows. Another
is to create a new table, and put the rows to be retained in that. The
latter approach allows you to use aggregates like FIRST, LAST, MIN, and MAX
to choose what the finished table looks like. Deleting rows can accomplish
the same, but may be more difficult.

When you start working this out, be very sure you have a copy of the
database. If your deletions work in a way you don't like, you'll be in a
fix if you cannot restore the data and try again.

Tom Ellison