带有 WHERE 子句和 INNER JOIN 的 MySQL 更新查询不起作用

Question

我的更新查询似乎无法进行下一步。我能够成功查看与选择相关的列没问题:

SELECT sales_flat_order_grid.entity_id,sales_flat_order_grid.increment_id,sales_flat_order.coupon_code
FROM sales_flat_order_grid 
INNER JOIN sales_flat_order ON sales_flat_order_grid.entity_id = sales_flat_order.entity_id     
WHERE sales_flat_order_grid.increment_id = "12345678";

这显示了 3 列，所有列都与正确的 increment_id 相关。

下一步是更新 sales_flat_order.coupon_code 字段。这是我的尝试:

UPDATE sales_flat_order 
INNER JOIN sales_flat_order ON sales_flat_order_grid.entity_id = sales_flat_order.entity_id      
WHERE sales_flat_order_grid.increment_id = "12345678"
SET coupon_code = "newcoupon";

但我不断收到 Not unique table/alias: 'sales_flat_order' 错误消息。有人能指出我正确的方向吗？

Answer 1

查询应该如下所示，您已经加入了同一个表，因此存在唯一别名的问题。我添加了表别名以提高可读性。

UPDATE 
sales_flat_order sfo
INNER JOIN sales_flat_order_grid sfog 
ON sfog.entity_id = sfo.entity_id      
SET sfo.coupon_code = "newcoupon"
WHERE sfog.increment_id = "12345678" ;