CODEWITHSUNDEEP
mysqlsqldatabase
WDiniz
WDiniz

Reputation: 115

MySQL update with a subquery in safe update mode

I have the following database: Database model The following query calculates the total amount, that comes from multiplying the products prices and the respective quantity bought, from the orders that have the amount field equals to NULL: 

SELECT SUM(i.qtde * p.preco_unitario) FROM Produtos p
    INNER JOIN ItensPedido i ON i.id_produto = p.id_produto
    INNER JOIN Pedidos pd ON pd.id_pedido = i.id_pedido
    WHERE pd.vlr_total IS NULL
    GROUP BY i.id_pedido);

I can update a row in the Pedidos table using the following query:

UPDATE Pedidos p 
SET p.vlr_total = (SELECT SUM(i.qtde * p.preco_unitario) FROM Produtos p
    INNER JOIN ItensPedido i ON i.id_produto = p.id_produto
    WHERE i.id_pedido = 1
    GROUP BY i.id_pedido)
WHERE p.id_pedido = 1;

But I have to re-run this query for each row that I want to update, changing the two WHERE clauses.

What I need help to is to build a query that updates all registries with NULL amount using a single query (if possible), similar to the second one. My attempt in using the first query as a subquery gives an 1093 error: using the same target table in update in the subquery.

Upvotes: 1

Views: 1034

Answers (1)

DDeMartini
DDeMartini

Reputation: 337

You should be able to do that by changing this one line in your subquery this will set the subquery WHERE clause to match your records in the outer table.

WHERE i.id_pedido = 1

TO

WHERE i.id_pedido = p.id_pedido

Then you can remove the outer WHERE, unless you really want to just restrict it to p.id_pedido=1

Upvotes: 1

Related Questions