MySQL 在安全更新模式下使用子查询更新 [英] MySQL update with a subquery in safe update mode

问题描述

我有以下数据库:以下查询根据金额字段等于 NULL 的订单计算总金额，该金额来自乘以产品价格和购买的相应数量:

SELECT SUM(i.qtde * p.preco_unitario) FROM Produtos pINNER JOIN ItensPedido i ON i.id_produto = p.id_produtoINNER JOIN Pedidos pd ON pd.id_pedido = i.id_pedido其中 pd.vlr_total 为空GROUP BY i.id_pedido);

我可以使用以下查询更新 Pedidos 表中的一行:

更新 Pedidos pSET p.vlr_total = (SELECT SUM(i.qtde * p.preco_unitario) FROM Produtos pINNER JOIN ItensPedido i ON i.id_produto = p.id_produto哪里 i.id_pedido = 1GROUP BY i.id_pedido)哪里 p.id_pedido = 1;

但我必须为要更新的每一行重新运行此查询，更改两个 WHERE 子句.

我需要帮助的是构建一个查询，使用单个查询(如果可能)更新所有具有 NULL 数量的注册表，类似于第二个查询.我尝试将第一个查询用作子查询时出现 1093 错误:在子查询中的更新中使用相同的目标表.

解决方案

您应该能够通过更改子查询中的这一行来做到这一点，这将设置子查询 WHERE 子句以匹配您在外部表中的记录.

>

WHERE i.id_pedido = 1

到

WHERE i.id_pedido = p.id_pedido

然后你可以去掉外面的 WHERE，除非你真的想把它限制为 p.id_pedido=1

I have the following database: 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.

解决方案

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

这篇关于MySQL 在安全更新模式下使用子查询更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！