SQL Syntax Question

[moepower]

I have a table with three columns (first, last, and email). How do I write a DELETE command to find multiple records with the same email address and delete all but one.

ie.

John, Smith, JS@hotmail.com
John, Smiths, JS@hotmail.com

I want to key only on the email address and delete until only a single occurance of email.

Thakns,

 

[ljspop]

You might try something like this (substituting your table
name for 'temp')...

delete from temp t1
where rowid <> (select max(rowid)
from temp
where email = t1.email)

Good luck,

Bob Bob Lowell
ljspop@yahoo.com

 

[Kenrae]

Be careful, as this will delete ALL rows in the table but one. I suppose you're trying to do that:

DELETE FROM temp t
WHERE rowid <> (select max(rowid)
from temp
where t.email = 'email')
and t.email = 'email';

 

[moepower]

Thanks, That's exactly what I'm looking for.

 

[ljspop]

Hi,

Hey Kenrae, try my query again. The subquery
looks for the maximum rowid for this particular
email address (&quot;where email = t1.email&quot and
will not delete the row if the rowid is the
biggest rowid for _that_ email address. This
allows you to eliminate all duplicates in one
pass, rather than having to do one at a time.

Give it a shot...

Bob Bob Lowell
ljspop@yahoo.com

 

[Kenrae]

Eh, um, you're right. I didn't noticed the &quot;t1.&quot; in the subquery