Nothing better than doing everything by steps .. slowly ...
The first thing we are going to do is obtain the IDs that meet the conditions:
SELECT dni, max(pago_contado) as contado, max(regularizado) as regularizado
FROM detalle
GROUP BY dni
This will return us a table that will have ID, the max found in the payment_count column and the maximum found in the regularized column.
Now, about that query, we can filter it to get the IDs that we really want, that doing something like this:
(let's call A to the previous query)
SELECT A.dni
FROM A
WHERE contado + regularizado = 2
Why that where? because if in a column there was a 1, the max returned 1, and if the two returned 1, then the sum will give 2.
Now, if we call that table B, we could do the UPDATE of just those dni with something like that
UPDATE resumen SET ESTADO="valido"
WHERE dni exists (B)
now, obviously, we can gather all the questions
UPDATE resumen SET ESTADO="valido"
WHERE dni exists (
SELECT A.dni
FROM (
SELECT dni, max(pago_contado) as contado, max(regularizado) as regularizado
FROM detalle
GROUP BY dni)
WHERE contado + regularizado = 2)
NOTE:
In all this explanation, actually there is a query of more, because B can be suppressed if in A something like this is done:
SELECT dni, max(pago_contado) as contado, max(regularizado) as regularizado
FROM detalle
GROUP BY dni
HAVING max(pago_contado) > 0 AND max(regularizado) > 0;
The idea of not doing this input, was to explain the logic well.
Edition:
the having, has to be another condition then ...
The query that brings the records that meet should be:
SELECT dni, min(pago_contado+regularizado) as cuenta
FROM detalle
GROUP BY dni
HAVING min(pago_contado+regularizado) > 0;
In these cases, the record that adds 0 will not be left, therefore, that ID will be eliminated.