MySQL update query with WHERE clause and INNER JOIN does not work
Didn't seem to reach the next step in my update request. I can successfully view columns related to no problem selection:
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 where everything is associated with the correct increment_id value.
The next step is to update the sales_flat_order.coupon_code field. Here's 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 the message Not unique table / alias name: 'sales_flat_order' . Can anyone point me in the right direction?
source to share
The query should be like below, you joined the same table and hence the unique alias problem. I added a 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" ;
source to share
You need to update sales_flat_order
and join sales_flat_order_grid
- you joined 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";
source to share
Yo has two times sales_flat_order
, change withsales_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 the same table use ALIAS
UPDATE sales_flat_order AS sfo1
INNER JOIN sales_flat_order AS sfo2 ...
source to share