You should be able to write a delete query to do this:
An example follows (assuming key fields are char. If not you can use cast
or convert
pk_id is your primary key
The first subquery gets all rows where keyfields match
The second subquery makes sures you keep the first match. You can use
min(pk_id) or max(pk_id) or whatever criteria you need to use to select only
one row.
delete from mytable
where
keyfield1+keyfield2+.... in
(select keyfield1+keyfield2+...) from tbl_whatever
group by keyfield1+keyfield2+...
having count(*) > 1)
and
pk_id not in
(select min(pk_id) from tbl_whatever
group by keyfield1+keyfield2+...
having count(*) > 1
)
HTH
Don Vawter
www.cyberroomies.com
[EMAIL PROTECTED]
----- Original Message -----
From: "cf kaizen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, September 17, 2001 10:30 AM
Subject: Comparing Records - Deleting Duplicates
> I've got a table that has several records where key
> fields are duplicate and I need to remove the
> duplicate row (note the entire row is not duplicate).
> What I know I have to do is run a query on the
> recordset, sort by the fields in question, and compare
> two records at a time. If the second record is a
> duplicate, get rid of it, and continue (loop). I know
> that there is a way to move forward one record from
> the one selected, which would allow me to compare the
> key fields and remove the second one if necessary, but
> not sure how to go about it. Can anyone help?
>
> Thanks in advance,
>
> Andrew
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
