You can get a list of dupes to eat without a temp table with something like:
select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1
And then just iterate thru delete from yer_table where the id = the min(id) as fetched above.
Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age.
Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...
select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1
And then just iterate thru delete from yer_table where the id = the min(id) as fetched above.
Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age.
Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...