Delete dups in a table

[Dominick D.]

Hello, I have a table of 280K rows; in this table, I have many dup rows. It
looks like this:

101
102
..
..
20405
20405
20405
21000
21000

and so on. Now, I must not only remove the dups, which I know how to do, but
how do I also delete the original row? In other words, take 20405: I can
remove the dups by setting in a query, unique values to yes, but how do I
take out 20405 as well? I can't hunt and peck a table of over 280K and delete
manually, so what query statement can I use? I need to remove the dups and
the original row so that I can append this table to another table, with
removed dups and the original row. Thanks.

 

[Duane Hookom]

Am I correct in assuming you want to delete all records with 20405? If so,
you could try SQL like:

DELETE tbl280KRows.*, tbl280KRows.Field1
FROM tbl280KRows
WHERE tbl280KRows.Field1 In (SELECT Field1 FROM tbl280KRows GROUP BY Field1
HAVING Count(*) >1);

 

[Michel Walsh]

Append the rows to the other table WHERE that other table will have an index
not allowing duplicated values. You will get a message telling that 'xxx'
rows have not been appended due to unique constraint, but that is exactly
what you want.

That solution is not so immediate in MS SQL Server, though (since, by
default, the whole insert statement will be rolled back if there is an
error), but if you use Jet, there should be no problem: by default, Jet
insert rows which can be inserted and reject those which do not satisfy some
validations. If you use the UI, you can THEN decide to abort the whole
insert statement. But again, you won't since you know those duplicated
values have to not be included.


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

You seem to say you want to remove ALL 20405 rows (all the rows where there
are more than a single instance of the value). Is that true?

Or do you want to keep one row of the duplicates, but not the "first" one
entered?

Or do you want to do something else?

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