MySQL update query with WHERE clause and INNER JOIN not working

Question

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?

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