I'm trying to do a DELETE FROM on my large table (about 800 million rows) based on the contents of another, moderately large table (about 110 million rows). The command I'm using is:
DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records);
This process ran for about two weeks before I decided to stop it -- it was dragging down the DB server. I can understand long-running processes, but two weeks seems a bit much even for a big table.
Is this the best way to approach the problem? Is there a better way?
You need
delete from records r where not exists (select 1 from unique_records ur where ur.id = r.id);