Duplicates

[conceptmgt]

Hi

The code below is supposed to remove duplicates but it just keeps the same records. Any ideas?

Thanks

Gary

strsql = "CREATE TABLE "&gotId&"emailaddresses ("
strsql = strsql + " EmailAddress longtext,"
strsql = strsql + " EmailAddressID INT(4),"
strsql = strsql + " EmailAddressActive tinyint(1),"
strsql = strsql + " EmailClientID INT(4),"
strsql = strsql + " EmailFirstName varchar(100),"
strsql = strsql + " EmailSurname varchar(100),"
strsql = strsql + " EmailMaster tinyint(1),"
strsql = strsql + " EmailList1 tinyint(1),"
strsql = strsql + " EmailList2 tinyint(1),"
strsql = strsql + " EmailList3 tinyint(1),"
strsql = strsql + " EmailList4 tinyint(1)"
strsql = strsql + " ) "

strsql2 = "INSERT INTO "&gotId&"emailaddresses "
strsql2 = strsql2 + " SELECT DISTINCT EmailAddress, EmailAddressID, EmailAddressActive, EmailClientID, EmailFirstName,"
strsql2 = strsql2 + " EmailSurname, EmailMaster, EmailList1, EmailList2, EmailList3, EmailList4"
strsql2 = strsql2 + " FROM EmailAddresses"

strsql3 = "DELETE FROM EmailAddresses"

strsql4 = "INSERT INTO emailaddresses "
strsql4 = strsql4 + " SELECT EmailAddressID, EmailAddress, EmailAddressActive, EmailClientID, EmailFirstName,"
strsql4 = strsql4 + " EmailSurname, EmailMaster, EmailList1, EmailList2, EmailList3, EmailList4"
strsql4 = strsql4 + " FROM "&gotId&"emailaddresses"

strsql5 = "DROP TABLE "&gotId&"emailaddresses"

 

[JamesLean]

The DISTINCT keyword will only deduplicate if the entire row is the same. Is this what you are trying to do or do you only have duplicates in one or two columns? --James

 

[gny]

If unique email addresses is what you are after, try this:

strsql = strsql + " EmailAddress longtext UNIQUE, "