Select and delete duplicate records by last update date?

Question

Select and delete duplicate records by last update date?

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Between the table fields there is the field name, the field dt_add which is the date the record was inserted and dt_update which is the date of the last update of the record.

I would like to know how to return and delete repeated records, for example if there are 100 records with equal names, delete those with last update date less than 01/11/2017

Explaining better:

In a system was found a problem that resulted in insertions we say from more than 16 thousand repetitions to about 100 records with the same name and value in a given table.

It seems surreal but more than 16,000 duplicate records for each value and the error occurred with about 100 values. The error was noticed after analysis due to the high consumption of server resources, and it has been observed that the error has existed since 2014 due to the dates of insertions and updates of the records.

What will be done is to delete all those that are repeated with date less than 11/01/2017 in order to lessen the consumption of resources and the system will be corrected.

I do not know if I could be clear enough for the context.

In summary, I need a SQL select to return all repeated records and a delete in order to delete repeated records dated less than 01/11/2017.

About primary key

It has a primary key, but it is a table of configurations where there should be unique registers (if not this problem) and the system is based on the configuration name and value reference.

Example:

nome : habilitar-alertas 
valor: true 

nome : bloquear-clientes-pendentes 
valor: false

php mysql sql mysqli

asked by anonymous 07.11.2017 / 20:48

2 answers

1

I made the following query:

  delete t.* from configuracoes t
     inner join (select 
c.nome,
c.valor,
c.date_add,
c.date_upd
from configuracoes c
where c.date_upd = (select max(x.date_upd) from configuracoes x where x.nome = c.nome)) x 
        where x.nome = t.nome and x.date_upd != t.date_upd;

SQLFiddle did not allow putting the DML command on the execution side, but it worked perfectly on the schema side.

Result:

Current Table Data:

AfterrunningDelete:

Important:Asyoumentioned,itisatableofsettingsandshouldonlyhaveonelineforeachconfiguration,Ididnotdeletewiththeconditionofthedatebeingsmallerthan01/11/2017andyes,deleteallduplicates,andkeeponlythelatest.

SQLFiddleWithoutthedeletecommand: link

SQLFiddle After the delete command: link

07.11.2017 / 22:14

How to integrate and make queries with pymongo? MemoryError in pandas

score 0 · Accepted Answer

I found the answer

Suppose all records where records with more than 2 replicates exist:

SELECT 
    nome, valor, dt_add, dt_update, COUNT(*) 
FROM 
    configuracoes 
GROUP BY 
    nome
HAVING 
    COUNT(*) > 2 
ORDER BY 
    dt_update DESC

To view records sorted by ID

SELECT 
    nome, valor, dt_add, dt_update 
FROM 
    configuracoes 
WHERE 
    nome = "habilitar-alertas"
 ORDER BY 
    id_configuracoes DESC

Delete with ID less than current:

DELETE FROM 
    configuracoes 
WHERE 
    nome = "habilitar-alertas" and id_configuracoes < "id_mais_atual" ;