I need to update the value of one SQL table with the sum of the values of another

Question

I need to update the value of the column total_services of a certain number with the value (value value value * quantity) of the table nfpse_detail

The command I’m using to try to do this is this

UPDATE nfpse, nfpse_detalhe
SET total_serviços = (valor_unitario * quantidade)
WHERE nfpse.numero = nfpse_detalhe.numero
AND nfpse.numero = 000004;

The value returned after this is 16000, but the result I want to receive is 41000 (4000 * 4) + (5000 * 5) That is, the command is only doing the account of the first line where the number is 000004, but it has two lines where the number is equal to 000004. How do I return the (value * quantity) of the two lines?

Answer 1

Use a subselect with the aggregation function SUM along with GROUP BY numero.

UPDATE nfpse
SET total_serviços = (SELECT SUM(valor_unitario * quantidade)
                      FROM nfpse_detalhe
                      WHERE nfpse.numero = nfpse_detalhe.numero
                      AND nfpse.numero = 000004
                      GROUP BY nfpse.numero);