Delete duplicate records

[Danny007]

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

[-__cfSearching__-]

> 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?

[Danny007]

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__-]

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

[Danny007]

What does ex stand for?

[Dan_Bracuk]

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__-]

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".

[Danny007 · Accepted solution]

Perfect. The query worked great. Thank you!