MS SQL to MYSQL

[conceptmgt]

Hi

I had a system that works fine in MS SQL 7 and I am converting it to MYSQL. I had a script that removes duplicates below:

strsql = "DELETE FROM EmailAddresses WHERE"
strsql = strsql + " EXISTS ("
strsql = strsql + " SELECT"
strsql = strsql + " NULL"
strsql = strsql + " FROM"
strsql = strsql + " EmailAddresses b"
strsql = strsql + " WHERE"
strsql = strsql + " b.[EmailAddress] = EmailAddresses.[EmailAddress]"
strsql = strsql + " GROUP BY"
strsql = strsql + " b.[EmailAddress]"
strsql = strsql + " HAVING"
strsql = strsql + &quot; EmailAddresses.[EmailAddressid] < MAX(b.[EmailAddressid])&quot;
strsql = strsql + &quot; )&quot;

It won't work on the MYSQL database any ideas?

Thanks

Gary

 

[swampBoogie]

You need to do it in multiple steps

create temporary table t(emailaddresses varchar(100))
insert into t select distinct EmailAddress from EmailAddresses
delete from EmailAddresses
insert into EmailAddresses select * from t
drop table t

Going from mssql to Mysql will requeire a lot of rewrite due to the lack of standard sql features in Mysql. If you are looking for a free dbms there are others that will be eaasier to port to, e.g. postgres, firebird and SAP.