使用WHERE子句和内部连接不工作的MySQL更新查询

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无关的列:

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

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

这将显示与正确的increment_id相关的3列。

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

下一步是更新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";

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

但是我一直得到一个不唯一的表/别名:'sales_flat_order'错误消息。有人能给我指出正确的方向吗?

3 个解决方案

#1

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

#2

You need to update sales_flat_order and join on sales_flat_order_grid - you've joined on sales_flat_order:

您需要更新sales_flat_order并加入sales_flat_order_grid——您已经加入了sales_flat_order:

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

#3

Yo have two times sales_flat_order, change with sales_flat_order_grid

您有两倍的sales_flat_order，使用sales_flat_order_grid进行更改

UPDATE sales_flat_order
INNER JOIN sales_flat_order_grid ... -- Need change the namme of the table

If you want to join with the same table, use ALIAS

如果希望与相同的表连接，请使用ALIAS

UPDATE sales_flat_order AS sfo1
INNER JOIN sales_flat_order AS sfo2 ...

#1