Update Where Column not in Select

Question

I am trying to update customers where their website_id is equal 6 and their email is not duplicate when website_id is 3.

I have been able to get all customer where website_id is 6 and their email is not duplicate where website_id is 3, the SQL statement bellow does that.

SELECT
  *
FROM customer_entity
WHERE website_id = 6
AND email NOT IN (SELECT
  email
FROM customer_entity
WHERE website_id = 3);

Now when I try to update all customers that have website_id and store_id equal to 3 to 6 where their email is not duplicate in store_id = 3

UPDATE customer_entity customers
    SET customers.website_id = 3, customers.store_id = 3 
    WHERE customers.website_id = 6 AND customers.email 
      NOT IN (SELECT email FROM customer_entity WHERE website_id = 3);

I get the following error

You can't specify target table 'customers' for update in FROM clause

How can I achieve what I am trying to do?

EDIT: I have also tried without the alias and I still get the same error.

Answer 1

Here is the answer for my own question. Many of the answer here use JOINS to one table so for MYSQL, this does not work because you can't update on the same SELECT JOIN.

My solution was to create a temporary table and store all values which had store_id and website_id = 6 and WHERE the email was not duplicate WHERE store_id and website_id = 3.

CREATE TEMPORARY TABLE customer_temp_table(
   SELECT email FROM customer_entity 
      WHERE 
         website_id = 6 
      AND  
         email NOT IN (SELECT email FROM customer_entity WHERE website_id = 3));

Then I update the the customer entity table using results from the temp table.

UPDATE customer_entity AS customers SET customers.website_id = 3, customers.store_id = 3
    WHERE customers.email IN (SELECT email FROM customer_temp_table) AND customers.website_id = 6;