Question: I have a database table with 33265902 (thirty-three million two hundred sixty-five thousand nine hundred two) records in it.

Much of this is made up of duplicates.

It is causing me issues as any website that queries this table is causing load on the server.

What makes this table unique is two fields, email\_address and job\_id

My question is what is the best way to delete all duplicates leaving one good record per field criteria.

What is the most efficient way to do this without killing the server.

Answer: 
You can use limit

DELETE FROM table
[WHERE conditions]
LIMIT row\_count

call this query every 5mins. Just limit to 10000 data or add a little bit more
You can try this: (replace table\_name with your table name)

```
    WITH cte AS (    
    SELECT job_id , email_address ,    
    row_number() OVER(PARTITION BY job_id , email_address order by job_id ) AS [rn]    
    FROM table_name    
 )    
 DELETE cte WHERE [rn] > 1  

```

another method using temp table :

```
 select distinct * into ‪#‎tmptbl‬ From table_name    
 delete from table_name    
 insert into table_name    
 select * from #tmptbl drop table #tmptbl   

```