Update table of duplicate records with conditions

Question

hello friends I have a table and I would like to update the records in order to take the NAMES that repeat and put as ACTIVE = 0 only which DATA_CADASTRO are old!
table:

+--------------------------------------------------+
|                  pessoas                         |
+----+--------+------+---------------------+-------+
| id | nome   | time |    data_cadastro    | ativo |
+----+--------+------+---------------------+-------+
| 1  | luiz   |  A   | 2019-10-05 10:52:01 | 1     |
+----+--------+------+---------------------+-------+
| 2  | carlos |  B   | 2019-10-20 20:52:01 | 1     |
+----+--------+------+---------------------+-------+
| 3  | maria  |  C   | 2019-09-15 10:50:30 | 1     |
+----+--------+------+---------------------+-------+
| 4  | maria  |  C   | 2019-09-16 10:40:50 | 1     |
+----+--------+------+---------------------+-------+
| 5  | carlos |  B   | 2019-10-20 20:55:01 | 1     |
+----+--------+------+---------------------+-------+

the result you would like to appear

+--------------------------------------------------+
|                  pessoas                         |
+----+--------+------+---------------------+-------+
| id | nome   | time |    data_cadastro    | ativo |
+----+--------+------+---------------------+-------+
| 1  | luiz   |  A   | 2019-10-05 10:52:01 | 1     |
+----+--------+------+---------------------+-------+
| 2  | carlos |  B   | 2019-10-20 20:52:01 | 0     |
+----+--------+------+---------------------+-------+
| 3  | maria  |  C   | 2019-09-15 10:50:30 | 0     |
+----+--------+------+---------------------+-------+
| 4  | maria  |  C   | 2019-09-16 10:40:50 | 1     |
+----+--------+------+---------------------+-------+
| 5  | carlos |  B   | 2019-10-20 20:55:01 | 1     |
+----+--------+------+---------------------+-------+

I’m trying like this:
"UPDATE people ACTIVE SET=0 WHERE NAME= 'maria'" however this changing all registration name='maria' without picking up the oldest data_cadastro
I accept all help and improvements...

Answer 1

For this you need a sub-query or a Join to resolve, because the records to be changed are those that appear more than once, and with the date different from the maximum.

These records could be returned with something like this:

SELECT nome, max(data_cadastro) data_cadastro
          FROM pessoas 
         GROUP BY nome;

That is, brings the records that are the most current (max(data_cadastro)). You would have to do a query where you do the updates of all but these, but it would be simpler to leave all as active = 0 and then only those for active = 1.

To do this, just make a Join of that sub-query in the update, thus:

UPDATE pessoas SET ativo = 0;

UPDATE pessoas p 
   JOIN 
      ( SELECT nome, max(data_cadastro) data_cadastro
          FROM pessoas 
         GROUP BY nome
      ) p2
    ON p2.nome = p.nome
   AND p2.data_cadastro = p.data_cadastro
    SET p.ativo = 1;

See here the example working: http://sqlfiddle.com

Answer 2

To resolve this, you first need to identify the repeating name records that are active. According to the table structure you entered, the following query can select the repeated ones.

select nome from pessoas where ativo = 1 group by nome having count(*) > 1

Taking just the example of Maria, to take the oldest asset you could do so

select id from pessoas where nome = 'Maria' and ativo = 1 order by data_cadastro LIMIT 1

Her update would look like this

update pessoas set ativo = 0 WHERE id in (select id from pessoas where nome = 'Maria' order by data_cadastro LIMIT 1)

It would be more practical to change all the repeated names at once. You can disable those with the older ID. The following query takes all the older Ids of the repeated names.

select min(id) from pessoas where nome in (
select nome from pessoas where ativo = 1 group by nome having count(*) > 1
) GROUP BY nome

Based on this result, you can do your update

update pessoas set ativo = 0 WHERE id in (
select min(id) from pessoas where nome in (
select nome from pessoas where ativo = 1 group by nome having count(*) > 1
) GROUP BY nome)