moving data to another table and deleting it from original

[nkomokov]

I want to move all fields of all records that have a duplicate email address to another table so that I can sort through those at my leisure while still having the previous table without any duplicates for email blasts. I have this code to find duplicate email records, but I'm not sure how to move them to a new table and delete them from the original.

Select 	a.Email,
a.*
from subscribers a
join 
(select Email 
from subscribers
group by Email
having count(*) > 1) b  
on a.Email = b.Email
order by a.Email

 

[jbenson001]

Select a.Email,
a.*
Into <your table>
from subscribers a
join
(select Email
from subscribers
group by Email
having count(*) > 1) b
on a.Email = b.Email
order by a.Email


delete
from
subscribers a
join
(select Email
from subscribers
group by Email
having count(*) > 1) b
on a.Email = b.Email
order by a.Email


The delete is untested. Make sure you test on some test tables.

 

[druer]

Be careful on the delete that you aren't deleting both of the records, instead of just the duplicate ones. To do the delete and remove just the second, third, fourth etc instances you would need to utilize some other field value that you could find the MIN to keep or the MAX to keep or whatever. But I think as the delete is done in the example it will remove both/all copies, not just the duplicates.