I'm trying to make the SUM
of two fields different and give a UPDATE
in another table.
I'm doing this:
UPDATE
pedidos a
SET
custo = (
SELECT
case when SUM((b.qtd - b.qtd_devolucao) * b.custo) is null then '0' else SUM((b.qtd - b.qtd_devolucao) * b.custo) end AS total
FROM
produtos_pedidos b
WHERE
b.id_pedido = a.id
)
valor = (
SELECT
case when SUM((b.qtd - b.qtd_devolucao) * b.valor) is null then '0' else SUM((b.qtd - b.qtd_devolucao) * b.valor) end AS total
FROM
produtos_pedidos b
WHERE
b.id_pedido = a.id
)
where
id = '2'
But mysql returns me a syntax error in the second SUM
.
If I do the separate queries they work, for example:
UPDATE
pedidos a
SET
custo = (
SELECT
case when SUM((b.qtd - b.qtd_devolucao) * b.custo) is null then '0' else SUM((b.qtd - b.qtd_devolucao) * b.custo) end AS total
FROM
produtos_pedidos b
WHERE
b.id_pedido = a.id
)
where
id = '2'
UPDATE
pedidos a
SET
valor = (
SELECT
case when SUM((b.qtd - b.qtd_devolucao) * b.valor) is null then '0' else SUM((b.qtd - b.qtd_devolucao) * b.valor) end AS total
FROM
produtos_pedidos b
WHERE
b.id_pedido = a.id
)
where
id = '2'
Well, I think that in order to be more efficient, it is better to join the queries, is it possible to do this?