SQL Delete duplicates problem - Access 2K

[kxcntry99]

Its a little unorthadox and may not be applicipable in your case but I have had great success with a create table query and a bunch of group by's to combine the dups into one record and then insert the one record into a new table.

Again it doesn't always work depending on the situation.

[Ecniv]

Usually it is a pain in the butt to delete duplicates.
Flagging them however is much easier

You can (I think) use an update query and a field to flag the dulpicate.
Question is do you want to flag the original / first as well as the dups or just the dups (newer records)?


One way.

UPDATE <table>
SET <dupflag> = true
WHERE <id> in (
-- sub query goes here
)

The sub query needs to return just the ids you are interested in. If you are flagging any dup you just need to

SELECT <table>.<id>
FROM <table> INNER JOIN (
    SELECT <phoneno>, count(<phoneno>) 
    FROM <Table> 
    GROUP BY <phoneno> 
    HAVING Count(<phoneno>) >1) as sq
ON <table>.<phoneno> = sq.phoneno

<table> is the tablename (surround with square brackets)
<phoneno> is the phonenumber field name (surround with square brackets)
sq is the alias for the sub-sub query.

The above flags all duplicated rows.
If you only want those that aren't the first record you need to get creative with subqueries
Or run in two steps. First one above to get the records.
Second one to update those highlighted back to false if they are the first record for a common field (ie customerid).

Another route is via code; sort the data and loop through. If you have one (or more) fields that are key and haven't changed, then flag the record as a duplicate.

Your choice if you want to delete it then, or just add filters in your queries to remove them.