Delete duplicate ip address entry in mysql database

Question

I have duplicate IP Address records in my database like this :

id | ipaddress
1    192.168.xxx.xxx
2    192.168.xxx.xxx
3    111.118.xxx.xxx
4    111.118.xxx.xxx

I want unique ip addresses in my field. How should I delete all duplicate entry?

Thanks

After you delete the duplicates, put a unique constraint on that column. — Mike Sherrill 'Cat Recall', Oct 27 '12 at 13:02

score 4 · Accepted Answer · 2012-10-27T12:30:41.950

4

Deleting duplicates in MySQL is a bit tricky because of the stupid restriction that the table cannot be referenced in a sub-select. Therefor the sub-select needs to be re-written into a join:

DELETE d
FROM mytable d
LEFT JOIN (
   SELECT min(id) as min_id
   FROM   mytable
   GROUP BY trim(ipaddress)
) tokeep ON tokeep.min_id = d.id
WHERE keep.min_id IS NULL;

SQLFiddle demo: http://sqlfiddle.com/#!2/9cfb9c/1

Edit

There is actually a way to get around the stupid sub-select restriction. If the table is wrapped into a derived table inside the sub-select, the MySQL parser doesn't notice this and happily deletes with a sub-select:

delete mt 
from mytable mt
where exists (
    select * 
    from (
      select id, ipaddress
      from mytable
    ) ex
    where TRIM(ex.ipaddress) = TRIM(mt.ipaddress)
   and ex.id < mt.id
)

edited Oct 27 '12 at 12:30

answered Oct 27 '12 at 12:19

Now that's an ugly syntax construct to hide the selfjoin. – wildplasser Oct 27 '12 at 12:26
@wildplasser: The selfjoin is not "hidden". It's the sub-select that needs to be worked around. And I agree: it's ugly (and a *really* stupid restriction). See my edit for a *really* ugly workaround. – Oct 27 '12 at 12:26
The braced version is a clever way to play hide-and-seek with the plan-generator. Does it force a "materialised" sub-select result table? NOTE: the grouping subselect appears to work as well. – wildplasser Oct 27 '12 at 12:33
@wildplasser: I have no idea. And I'm actually not sure if this is "supported". Maybe it will do something completely different in the background without telling - after all it's MySQL – Oct 27 '12 at 12:34

wildplasser · Answer 2 · 2012-10-27T12:30:14.803

CREATE TABLE mytable
        (id SERIAL NOT NULL PRIMARY KEY
        , ipaddress varchar
        );
INSERT INTO mytable(id, ipaddress) VALUES
 (1, '192.168.xxx.xxx')
,(2, '192.168.xxx.xxx ')        --<< note trailing whitespace
,(3, '111.118.xxx.xxx')
,(4, '111.118.xxx.xxx')
        ;
SELECT * FROM mytable;

DELETE FROM mytable mt
WHERE EXISTS (
  SELECT * FROM mytable ex
  WHERE ex.ipaddress = mt.ipaddress
  AND ex.id < mt.id
  )
  ;
SELECT * FROM mytable;

DELETE FROM mytable mt
WHERE EXISTS (
  SELECT * FROM mytable ex
  WHERE TRIM(ex.ipaddress) = TRIM(mt.ipaddress)
  AND ex.id < mt.id
  )
  ;
SELECT * FROM mytable;

OUTPUT:

CREATE TABLE
INSERT 0 4
 id |    ipaddress     
----+------------------
  1 | 192.168.xxx.xxx
  2 | 192.168.xxx.xxx 
  3 | 111.118.xxx.xxx
  4 | 111.118.xxx.xxx
(4 rows)

DELETE 1
 id |    ipaddress     
----+------------------
  1 | 192.168.xxx.xxx
  2 | 192.168.xxx.xxx 
  3 | 111.118.xxx.xxx
(3 rows)

DELETE 1
 id |    ipaddress    
----+-----------------
  1 | 192.168.xxx.xxx
  3 | 111.118.xxx.xxx
(2 rows)

UPDATE: added testdata and changed one record to have trailing whitspace.

NOTE: the names of the stringfunctions may vary between DMBS implementations. The TRIM() function works for postgres, maybe mysql has another name for the same thing.

UPDATE2: since mysql appears to not allow selfjoins in delete statements a workaround would be to use an auxillary table with the ids of the records you (don't) want to keep.

(the solution by @ahose_with_no_name is shorter, but this one tries to keep close to the plain-vanilla SQL) :

CREATE table without_dups(id INTEGER NOT NULL);
INSERT INTO without_dups(id)
SELECT id
FROM mytable mt
WHERE NOT EXISTS (
  SELECT * FROM mytable ex
  WHERE ex.ipaddress = mt.ipaddress
  AND ex.id < mt.id
  )
  ;

DELETE FROM mytable mt
WHERE NOT EXISTS (
  SELECT * FROM without_dups nx
  WHERE nx.id = mt.id
  )
  ;

DROP TABLE without_dups;

SELECT * FROM mytable;

Maybe ipaddress is a varchar(xxx) field and some of the records have trailing whitespace ? — wildplasser, Oct 27 '12 at 12:02
It showing the following error : `MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mt WHERE EXISTS (SELECT * FROM mytable ex WHERE ex.ipaddress = mt.ipaddre' at line 1 ` — J.K.A., Oct 27 '12 at 12:08
MySQL does not allow the table you are deleting from to be used in a sub-select. — , Oct 27 '12 at 12:09
I'm sorry. In that case I am afraid I can't help the OP. OTOH: maybe tuck it into a view? Or a CTE ? ;-) — wildplasser, Oct 27 '12 at 12:10
A bit nitpicking on terminology: MySQL's restriction isn't for a selfjoin. It's for a sub-select (including co-related ones). My solution does use a self-join (but not a sub-select) — , Oct 27 '12 at 12:32

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Try this

DELETE * FROM MyTable AS aa INNER JOIN (
  SELECT MIN(id) as MID, id, ipaddress FROM MyTable
  GROUP BY id, ipaddress HAVING COUNT(*) > 1
) AS bb ON bb.id = aa.id AND bb.ipaddress = aa.ipaddress
  AND bb.MID <> aa.id;

Visit this link

edited May 23 '17 at 12:07

Community

1
1

answered Oct 27 '12 at 12:30

Gnik

7,120
20
79
129

Delete duplicate ip address entry in mysql database

3 Answers3