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

问题描述

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

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

但是我一直收到 不是唯一的表/别名:"sales_flat_order" 错误消息.有人可以指出我正确的方向吗?

解决方案

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

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

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

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

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

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屋！