Run 2 `SUM` on a `UPDATE`

Asked

Viewed 373 times

1

Well I’m trying to do the SUM of two different fields and give a UPDATE in another table.

I’m doing like 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'

However mysql returns a syntax error in the second SUM. If I do the separate query they work, 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 to be more efficient is better I join the query, it is possible to do this?

mysql

1 answer

2


One comma was missing ,, after the closing of the parentheses of the select country 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'

  • Hahahah and even, thank you.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.