Removing duplicate entries in an SQL table?

Question

I did research but the answers were too complicated to convert to my schema and solution.

I have a table which I forgot to make a field unique in and now the insert has created lots and lots of items under the same field value. My table name is queue_items and the field is called item - how can I remove duplicates of item field?

I still want to be left with 1 item of the duplicates if that makes sense, but just delete any more than 1.

Here is what I've got so far

WITH CTE AS(
   SELECT `item`
       RN = ROW_NUMBER()OVER(PARTITION BY `item` ORDER BY `item`)
   FROM `queue_items`
)
DELETE FROM CTE WHERE RN > 1

try to create auto-incremental primary key, then you can query against it - check for minimum value / group per item or any other similar approach — Anatoliy R, Nov 16 '19 at 19:10
Do you have a primary key? If not, do you care which record you keep? — Nick, Nov 16 '19 at 20:18
Does this answer your question? https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — kulsin, Nov 16 '19 at 20:35

score 0 · Accepted Answer · answered Nov 16 '19 at 20:32

0

If you have a primary key like e.g. id you could try something like:

DELETE FROM
  queue_items
WHERE
  id
NOT IN (
  SELECT MIN(id) FROM queue_items GROUP BY item
);

answered Nov 16 '19 at 20:32

harpocrates

140
7

score 0 · Answer 2 · answered Nov 16 '19 at 20:39

I would suggest emptying the table and repopulating it:

CREATE temp_qi AS
    SELECT i.*
    FROM (SELECT qi.*,
                 ROW_NUMBER()OVER(PARTITION BY `item` ORDER BY `item`) as seqnum
          FROM `queue_items`
         ) qi
         WHERE seqnum = 1;

ALTER TABLE drop_column seqnum;

TRUNCATE TABLE queue_items;   -- backup before doing this!

INSERT INTO queue_items
    SELECT *  -- columns should be in the right order
    FROM temp_qi;

score 0 · Answer 3 · edited Nov 16 '19 at 21:58

Well that was s tricky one :) As far As I noticed you have a SQL-Table with Duplicate Entries, no Unique-Key (of course) and now simply want to get rid of the duplicates. I tried to recreate this using mySQL, which is not that easy for one can not DELETE / UPDATE and Querying to the same Table in mySQL per Definition.

Instead I had to follow this simple workaround:

Create a new TempTable, with the same structure as the original table
Copy every entry, to the new TempTable BUT group them by the duplicate_ID
DELETE original table
RENAME TempTable to the original table's name

In SQL you can do so by running the following queries - but make sure you have a backup, just in case.... :)

Workaround Delete duplicate SQL entries:

CREATE TABLE newTestTabelle LIKE TestTabelle;
    //make sure table was created successfully before next step

INSERT INTO newTestTabelle
    SELECT * FROM TestTabelle
    GROUP BY myIndex; 
    //make sure copy was successfully done

DROP TABLE TestTabelle;

ALTER TABLE newTestTabelle RENAME TO TestTabelle;

Hint: I found a similar solution and very nice documentation for that under following link (http://www.mysqltutorial.org/mysql-delete-duplicate-rows/) - read for further information on topic

Removing duplicate entries in an SQL table?

3 Answers3