How to find and delete duplicate records from table

Please note that this may work with other db systems like oracle, mysql, mssqol or sqlite.

Select all duplicates with id lower than max(id):

SELECT i.item_id FROM items AS i WHERE i.item_id != (
   SELECT max(items.item_id) FROM items WHERE items.name = i.name
)

Delete all duplicates with id lower than max(id):

SELECT i.item_id FROM items AS i WHERE i.item_id != (
   SELECT max(items.item_id) FROM items WHERE items.name = i.name
)

Alternative way using exists

Second way. You can always use @WHERE [NOT] EXISTS@ statement.

DELETE FROM items WHERE item_id IN (
   SELECT item_id FROM items WHERE EXISTS(
      SELECT * FROM items AS i WHERE i.item_id > items.item_id AND items.name = i.name
   )
);