perform query and condition update

1

Specifically, I tried a lot of things and it did not work out or I got messed up trying so hard.

I have 2 tables Summary and Detail

What I'm trying to do is get all records for a DNI in the DETAIL table, on the result check the values of "payment_count" and "regularized", if it is true that all the records have as a value 1 in any of the 2 fields the status of the DNI in the SUMMARY table is updated.

How do I do it? I clarify that these tables were created for the example with the idea of explaining the concept as best as possible. I hope you understand what I wanted to say haha

Summary Table

Detail Table

Example:

    
asked by Pablo Malbran 04.04.2018 в 04:47
source

2 answers

1

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.

    
answered by 04.04.2018 / 05:33
source
0

with the idea of @alo Malbarez and the logic of @gbianchi come to this. It works

UPDATE resumen set estado = 'Validado' 

WHERE dni = (SELECT b.dni FROM (SELECT * , sum(a.pago_contado + 
a.regularizado) suma, count(*) contador FROM 'detalle' a GROUP by a.dni) 

b WHERE b.suma = b.contador)
    
answered by 04.04.2018 в 07:12