Run 2 'SUM' on an 'UPDATE'

1

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?

    
asked by anonymous 28.07.2017 / 23:41

1 answer

2

A comma , has been missing after closing the parentheses of select of field custo :

    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'
    
29.07.2017 / 00:08