Delete duplicate records

Forum|Forum|17 years ago
August 15, 2008
3 replies
811 views

Hi,

I would like to delete duplicate records that have the same emal address. It does not matter what record is deleted out of the duplicates. I just need to match a forum number column.

Can anyone suggest the best way to do this?

TIA

Database access

This topic has been closed for replies.

Correct answer Danny007

Perfect. The query worked great. Thank you!

-

-__cfSearching__-

Inspiring

It is an alias or a shorthand way of referring to a table. So can write "ex.columnName" instead of spelling out the whole table name "tablename.columnName".

D

Danny007AuthorCorrect answer

Participant

Perfect. The query worked great. Thank you!

D

Dan_Bracuk

Inspiring

delete from yourtable
where id in
(
select maxid from
(
select email, max(id) maxid
from yourtable join
(select email eml, count(email)
from yourtable
group by email
having count(email) > 1
group by email
) abc on email = eml
)
)
You might have to do it more than once, depending on how many times an email is duplicated. You'll have to deal with any foreign key issues first.

-

-__cfSearching__-

Inspiring

> I would like to delete duplicate records that have the same emal address. It does not matter what
> record is deleted out of the duplicates. I just need to match a forum number column.

I am not certain what you mean by "match a forum number column". Does your table have a unique record identifier and what database type are you using?

D

Danny007Author

Participant

I'm using MSSQL with a datasource name for coldfusion. Here are the column and dbname:

DBName = email_addresses
EmailID = unique ID
ListID = This has a number and I only want to pull up records let's say from where ListID = 4
EmailAddress = This is the email address column.

Thank you!

-

-__cfSearching__-

Inspiring

One approach is to select the maximum record id for each email address with listID 4. That would be the record to keep. All other record ids (with listID 4) would be considered duplicates and would deleted. Something like this. You should make a backup of your data first!

DELETE FROM ex
FROM YourTable AS ex LEFT JOIN
(
--- get maximum record id for each email with ListID = 4
SELECT EmailAddress, MAX(EmailID) AS EmailID
FROM YourTable
WHERE ListID = 4
GROUP BY EmailAddress
)
AS mx ON ex.EmailID = mx.EmailID
WHERE ex.ListID = 4
AND mx.EmailID IS NULL

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.