Re: Removing duplicate records - OT

[Bart Lateur]

On Tue, 14 Aug 2001 11:12:23 +1000, Neil Lunn wrote:

Use something like this as the record source for items with duplicate email
addresses:

select *
from table
where email in (
  select email
  from table
  group by email
  having count(*)  1)

and delete every second record.

Next question: how do you delete every second record? Quickly?

-- 
Bart.

RE: Removing duplicate records - OT

[Neil Lunn]

-Original Message-
From: Bart Lateur [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 14, 2001 8:30 PM
To: [EMAIL PROTECTED]
Subject: Re: Removing duplicate records - OT


On Tue, 14 Aug 2001 11:12:23 +1000, Neil Lunn wrote:

Use something like this as the record source for items with 
duplicate email
addresses:

select *
from table
where email in (
  select email
  from table
  group by email
  having count(*)  1)

and delete every second record.

Next question: how do you delete every second record? Quickly?

$count = 0;
while ($sth-fetch) { # or whatever

   $count++;
   if ($count  1) {
   # delete statement here for Id = current Id
   $count = 0;
   }
   # Otherwise skip over it
}

I wasn't going to write all the code and I can't be quick when I'm not here.

--Neil


-- 
   Bart.


__
Please Note :
Only  the intended recipient is authorised to access or use this e-mail.
If you are not the intended recipient, please delete this e-mail and notify
the sender immediately. The contents of this e-mail are the writer's 
opinion and are not necessarily endorsed by the Gunz Companies
unless expressly stated.

We use virus scanning software but exclude all liability for viruses or
similar in any attachment.

Re: Removing duplicate records - OT

[Ronald J Kimball]

On Tue, Aug 14, 2001 at 11:12:23AM +1000, Neil Lunn wrote:

 Use something like this as the record source for items with duplicate email
 addresses:
 
 select *
 from table
 where email in (
   select email
   from table
   group by email
   having count(*)  1)
 
 and delete every second record.

This approach would end up deleting the wrong records if the records aren't
returned in the expected order (you could add an order by to fix that), or
if any email appears more than twice.

Ronald

RE: Removing duplicate records - OT

[Neil Lunn]

While I can read that the person who posted this message has solved their
issue, this might be worthwhile still.

One thing I notice is that all responses are processing very record to see
if a duplicate record is there. Hopefully though the database server is a
little more powerful than the machine running the script so why not do the
hash match there?

Use something like this as the record source for items with duplicate email
addresses:

select *
from table
where email in (
  select email
  from table
  group by email
  having count(*)  1)

and delete every second record.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, August 12, 2001 10:49 PM
To: [EMAIL PROTECTED]
Subject: Removing duplicate records - OT


Hello all,

Sorry about the off-topic question, but this has been driving 
me nuts. I am
trying to import a tab delimited file into a MySQL database 
and need to remove
duplicate email addresses, before importing the data. The file 
is setup like
this:

id1  user1  pass1  email1  name1  na  1  na
id2  user2  pass2  email2  name2  na  0  na
id3  user3  pass3  email2  name3  na  1  na
id4  user4  pass4  email   name4  na  1  na
etc
now I need to go thru each data record in the file and remove 
all duplicate
records where the email address is the same. IE above, id2 has 
the same email
address and id3 so I want to remove id3.. and so on. the file 
is sorted by the
id value.

Any help much appreciated.


Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


__
Please Note :
Only  the intended recipient is authorised to access or use this e-mail.
If you are not the intended recipient, please delete this e-mail and notify
the sender immediately. The contents of this e-mail are the writer's 
opinion and are not necessarily endorsed by the Gunz Companies
unless expressly stated.

We use virus scanning software but exclude all liability for viruses or
similar in any attachment.