带有 WHERE 子句和 INNER JOIN 的 MySQL 更新查询不起作用 [英] MySQL update query with WHERE clause and INNER JOIN not working

问题描述

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

Can't seem to reach the next step in my update query. I'm able to successfully view columns related to the select no problem:

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";

这显示了与正确的 increment_id 相关的 3 列.

This shows 3 columns all where related to the correct increment_id.

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

The next step is to update the sales_flat_order.coupon_code field. Here is my attempt:

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' 错误消息.有人能指出我正确的方向吗?

But I keep getting a Not unique table/alias: 'sales_flat_order' error message. Could someone point me in the right direction?

推荐答案

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

The query should be as below, you have joined the same table and hence the problem of unique alias. I have added table alias for better readability.

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" ; 

这篇关于带有 WHERE 子句和 INNER JOIN 的 MySQL 更新查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！