Duplicates

[conceptmgt]

Hi

I need help removing duplicates from a mySQL table. The sql statement below gives no errors but always comes back with the same records, so no duplicates are removed. The table has fields such as EmailAddress, EmailAddressID, EmailAddressActive, EmailClientID, EmailFirstName etc. So there could be two email addresses the same but the other fields could be different but I still want to remove the whole record.

I think it is something to do with part 2 of the sql statement.

Hope this makes sense.

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"

Set RS = Conn.Execute(strsql)
Set RS = Conn.Execute(strsql2)
Set RS = Conn.Execute(strsql3)
Set RS = Conn.Execute(strsql4)
Set RS = Conn.Execute(strsql5)

 

[harmmeijer]

What specifys as a duplicate (no row can be a dublicate because I assume the EmailAddressID is uniuqe).

You want the EmailAddress to be unique?
The easiest way to do that is to put a unique constraint on the EmailAddress column so inserting the same EmailAddress will cause an error that you can handle when a user tries to insert an EmailAddress that is allready there.

If you want to delete all (NOT like a distinct statement) colums that have a non unique EmailAddress then you can use a sql statement like this:
delete from mytable where myUniqueField in (select myUniqueField from mytable group by myUniqueField having count(myUniqueField)>2)
This statement will remove all records where the myUniqueField is not unique. You don't want this because you want to keep one.
Because you don't know which one I suggest moving the non unique rows to a refused table and then deleting the non unique EmailAddress rows. Like so:
insert into tblRefused
select * from EmailAddresses where EmailAddress in (select EmailAddress from EmailAddresses group by EmailAddress having count(EmailAddress)>2)

delete from EmailAddresses where EmailAddress in (select EmailAddress from EmailAddresses group by EmailAddress having count(EmailAddress)>2)

 

[conceptmgt]

Thanks

I tried:
insert into tblRefused
select * from EmailAddresses where EmailAddress in (select EmailAddress from EmailAddresses group by EmailAddress having count(EmailAddress)>2)

But I get this error:
[TCX][MyODBC]You have an error in your SQL syntax near 'select EmailAddress from EmailAddresses group by EmailAddress having count(Email' at line 1

 

[harmmeijer]

Tested this on sql server, you seem to use mysql.
Can you run sql statements in a dos box? (I've seen someone do it but don't use mySQL myselve).
It must be a little syntax error maybe a ( or a ' (hate those errors).
You can try this:
insert into tblRefused
select * from EmailAddresses
where EmailAddress in
(select EmailAddress from EmailAddresses
group by EmailAddress
having (count(EmailAddress)>2))

If you try a multi line sql statement the error at line... might give you more info I think it was the ( and ) that were missing.

 

[conceptmgt]

:-(

Still get the same error cannot seem to work out what it is!?!

Thanks

Gary