Hi Karam, I would suggest to CREATE A TEMP TABLE with SELECT DISTINCT ... query in your table, then do TRUNCATE TABLE to your existing table.. You can just add indexes before you insert back the records from the TEMP TABLE...
-- Richard Bornay ST Assembly Test Services Test Product Engineering Test Data Management Group 6824-1367 Karam Chand <[EMAIL PROTECTED]> 29/07/2003 01:13 AM To: Venelin Arnaoudov <[EMAIL PROTECTED]>, [EMAIL PROTECTED], [EMAIL PROTECTED] cc: [EMAIL PROTECTED], (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: Re: Deleting duplicating records Great. So in that case I need to create a new table with similar structure with a additional UNIQUE index on email. Karam --- Venelin Arnaoudov <[EMAIL PROTECTED]> wrote: > I would copy all the records (_email_, max(version)) > to a new table, > drop the old one and then rename the new one > > Regards, > Venelin > > Karam Chand wrote: > > >Well that is OK if I have only one email. > > > >What if if I have thousands of users duplicated... > > > >Do I need to write SQL query 1000 times.... > > > >Karam > >--- Jeff McKeon <[EMAIL PROTECTED]> wrote: > > > > > >>How bout.... > >> > >>Delete from tablename where email like > >>[EMAIL PROTECTED] && ID <> 1 > >> > >>Jeff > >> > >> > >>>-----Original Message----- > >>>From: Karam Chand > [mailto:[EMAIL PROTECTED] > >>> > >>> > >>>Sent: Monday, July 28, 2003 10:33 AM > >>>To: [EMAIL PROTECTED] > >>>Subject: Deleting duplicating records > >>> > >>> > >>>Greetings > >>> > >>>I manage a website wherein i keep track of the > >>> > >>> > >>people > >> > >> > >>>email who have downloaded my software and the > >>> > >>> > >>version > >> > >> > >>>number. > >>> > >>>the structure is like - > >>> > >>>id int auto_increment primary key, > >>>email char, > >>>version > >>> > >>>now the same person can download different > version > >>>therfore my table has data like this - > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>2,[EMAIL PROTECTED],2.0 > >>>3,[EMAIL PROTECTED],3.0 > >>> > >>>Now I want to delete all the records wherein all > >>> > >>> > >>rows > >> > >> > >>>with duplicate email addresses are deleted so > that > >>> > >>> > >>i > >> > >> > >>>have data like > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>... > >>> > >>>What should be the query? Thanks in advance. > >>> > >>>Karam > >>> > >>>__________________________________ > >>>Do you Yahoo!? > >>>Yahoo! SiteBuilder - Free, easy-to-use web site > >>> > >>> > >>design > >> > >> > >>>software http://sitebuilder.yahoo.com > >>> > >>>-- > >>>MySQL General Mailing List > >>>For list archives: http://lists.mysql.com/mysql > >>>To unsubscribe: > >>>http://lists.mysql.com/mysql?> > >>> > >>> > >>[EMAIL PROTECTED] > >> > >> > >>> > >>> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >> > >> > >> > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > >__________________________________ > >Do you Yahoo!? > >Yahoo! SiteBuilder - Free, easy-to-use web site > design software > >http://sitebuilder.yahoo.com > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]