rafpre rafpre - 3 months ago 16
MySQL Question

MYSQL update a column with a subtract in a inner join

I want to update column 'pagadas' from 'remisiones' with a calculated value from another 2 tables. My problem is I´m not be able to write the subtract into the inner join.

I´ve got this:

UPDATE remisiones AS r
INNER JOIN
(
SELECT remi, SUM(cantidad*precio) AS 'total'
FROM detalleremi
GROUP BY remi
)
AS d
ON r.id = d.remi
SET pagadas = 's'
WHERE d.total = 250000


This works too:

UPDATE remisiones AS r
INNER JOIN
(
SELECT remisionId, coalesce(SUM(cantidadp), 0) 'pagos'
FROM pagos
GROUP BY remisionId
)
AS d
ON r.id = d.remisionId
SET pagadas = 's'
WHERE d.pagos = 250000


But how can I subtract total - pagos ?

SELECT remi, SUM(cantidad*precio)
FROM detalleremi
GROUP BY remi -
SELECT remisionId, coalesce(SUM(cantidadp), 0)
FROM pagos
GROUP BY remisionId AS deuda


and set as:

SET pagadas = 's'
WHERE x.deuda = 0

Answer

Is this what you want?

UPDATE remisiones r INNER JOIN 
       (SELECT remi, SUM(cantidad*precio) as total
        FROM detalleremi
        GROUP BY remi
       ) d 
       ON r.id = d.remi INNER JOIN
       (SELECT remisionId, coalesce(SUM(cantidadp), 0) as pagos
        FROM pagos 
        GROUP BY remisionId
       ) p
       ON r.id = p.remisionId 
    SET pagadas = 's'
WHERE d.total = p.pagos