Delete duplicate records via php in mysql

Question

Good morning guys, I read some articles here with similar problems, but none of them answered my question, what I want. Here’s the thing:

I have a query to check which lines have a duplicate field

SELECT IP, Count(*) FROM hardware GROUP BY IP HAVING Count(*) > 1

Possession of the information of which ips are duplicated, I capture the data from the IP field in a variable and delete:

DELETE FROM hardware WHERE IP = '{$ip}'

Works well, but I would like to delete only the oldest record, keeping the latest.

Does anyone have any idea?

Answer 1

Run the querie below: It creates sub queries by filtering first those that have duplicate value, And then add the filter by removing the lower ID.

delete from hardware  where  hardware.id in 
(
  select * from  ( 
        select h1.id 
        from hardware h1 
        where h1.ip in ( SELECT h2.ip   FROM hardware h2   GROUP BY h2.IP  HAVING Count(h2.ip) > 1 )   
        and h1.id not in (SELECT min(h3.id) FROM hardware h3 GROUP BY h3.IP HAVING Count(h3.IP) > 1)  
   ) as subconsulta
)

See an example here http://sqlfiddle.com/#! 9/649a89/1

Answer 2

NOME_COLUNA is of type DATETIME

to delete records before 3 months

• DELETE FROM NOME_TABELA WHERE NOME_COLUNA < DATE_SUB(NOW(), INTERVAL 3 MONTH);

to delete records before 1 day

• DELETE FROM NOME_TABELA WHERE NOME_COLUNA < DATE_SUB(NOW(), INTERVAL 1 DAY);

to delete records of a specific date

• DELETE FROM NOME_TABELA WHERE NOME_COLUNA < '2016-06-10 07:00:00';

// to delete records of a specific date

• DELETE FROM NOME_TABELA WHERE DATE(NOME_COLUNA) < '2016-06-10';

To know how many lines will be deleted, you can use select below:

SELECT COUNT(*) FROM NOME_TABELA WHERE NOME_COLUNA < DATE_SUB(NOW(), INTERVAL 3 MONTH) To know the cut date, use:

• SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);

Answer 3

the SQL below should suit you:

DELETE FROM hardware
WHERE id IN (
  SELECT a1.id
  FROM hardware a1
  JOIN (
    SELECT
      MAX(id) id,
      IP
    FROM `hardware`
    GROUP BY IP
    HAVING COUNT(1) > 1
  ) a2
  ON a2.IP = a1.IP AND a1.id != a2.id
)

Explanation:

1. Subquery a2 selects the latest ID of duplicate IP records.
2. The parent subquery selects the id of all subquery a2 IP records, discarding the most recent IP records.
3. The DELETE expression removes records whose id exists in the list of older duplicate records.

Obs: If your table does not have an ID column, you can use a possible record creation date column, if any. If it does not exist, you can create the ID column, which will receive the order values of the records automatically by Mysql, with the following command:

ALTER TABLE hardware ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID' FIRST, ADD PRIMARY KEY (id);