mysql - 如何修复 MySQL 8.2 中表被指定两次的问题？

Question

这是我的查询:

UPDATE order_product
                SET amount = coalesce(
                    (
                        SELECT amount_origin + (SELECT sum(amount_to_change)
                                                 FROM order_product_edits 
                                                 WHERE order_product_id = NEW.order_product_id
                     )
                        FROM order_product
                        WHERE id = NEW.order_product_id
                    )
                , 0)

它在我的本地计算机(MySQL 8.3)上运行良好..但它在服务器(MySQL 8.2)上抛出此错误:

Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

知道如何修复它吗？

Answer 1

问题在于嵌套的相关子句。我认为你不需要它:

UPDATE order_product op
    SET amount = COALESCE(op.amount_origin + (SELECT sum(ope.amount_to_change)
                                             FROM order_product_edits ope
                                             WHERE ope.order_product_id = op.order_product_id
                                            ), 0
                         )
     WHERE op.id = NEW.order_product_id;

我很惊讶默认值是 0 而不是 op.amount_origin。