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.

updates get a little weird with aliases, might be able to rewrite it without the alias and it should work (at least in MS's flavor of SQL anyways) — user2366842, Apr 20 '17 at 19:42

score 0 · Answer 1 · answered Apr 20 '17 at 19:41

0

Remove the aliases from the code. Try this:

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

answered Apr 20 '17 at 19:41

C B

1,677
6
18
20

score 0 · Answer 2 · answered Apr 20 '17 at 19:42

0

You must be using MySQL. You can do this with a join. I would write this as:

update customer_entity ce join
       (SELECT email
        FROM customer_entity
        GROUP BY email
        HAVING SUM(website_id = 6) > 0 AND
               SUM(website_id = 3) = 0
      ) e
      on ce.email = e.email
    set ce.website_id = 3,
        ce.store_id = 3 ;

answered Apr 20 '17 at 19:42

Gordon Linoff

1,242,037
58
646
786

I get this error Duplicate entry 'secrete-email' for key 'UNQ_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID' – André Ferraz Apr 20 '17 at 20:00
Do you think its because you trying to update stuff with a join of the same table? – André Ferraz Apr 20 '17 at 20:40
By the way I am using percona not mysql – André Ferraz Apr 20 '17 at 20:43

RoMEoMusTDiE · Answer 3 · 2017-04-21T07:20:50.210

This is to correct or enhance your update statement

Update FROM and NOT EXISTS otherwise the basic UPDATE and NOT IN

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

UPDATE and NOT IN

 UPDATE customer_entity 
        SET website_id = 3
           ,store_id = 3 
        WHERE website_id = 6 
              AND email not in
                (SELECT email
                   FROM customer_entity 
                   WHERE website_id = 3
                );

however looking at this statement from your problem

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

this should be your solution : UPDATE and NOT EXISTS

UPDATE customers

        SET website_id = 6      -- Now when I try to update all customers that have website_id and store_id equal to 3 to 6
           ,store_id = 6 

        from customer_entity customers
        WHERE website_id = 3 and store_id = 3     -- Now when I try to update all customers that have website_id and store_id equal to 3 to 6

              AND NOT EXISTS 
                (SELECT 1 
                   FROM customer_entity c 
                   WHERE c.store_id = customers.store_id        -- where their email is not duplicate in store_id = 3 
                   and c.email = customers.email
                );

UPDATE and NOT IN

UPDATE customer_entity
        SET website_id = 6      -- Now when I try to update all customers that have website_id and store_id equal to 3 to 6
           ,store_id = 6 

        WHERE website_id = 3 and store_id = 3     -- Now when I try to update all customers that have website_id and store_id equal to 3 to 6

              AND email NOT IN
                (SELECT email 
                   FROM customer_entity c 
                   WHERE store_id = 3       -- where their email is not duplicate in store_id = 3 

                );

Probably coz the for sql-server or mysql. Perhaps what you can do is to format it to new database you're running against — RoMEoMusTDiE, Apr 21 '17 at 07:17
Not really, for example your last example you are saying "UPDATE customer_entity SET website_id = 6, store_id = 6 customer_entity". — André Ferraz, Apr 21 '17 at 07:18
Logically your answers are still incorrect. You can not update tables from table select. — André Ferraz, Apr 21 '17 at 07:50

André Ferraz · Accepted Answer · 2017-04-21T21:43:19.703

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;

score -1 · Answer 5 · edited May 23 '17 at 12:34

If you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM customer_entity), like this

SELECT * FROM customer_entity WHERE website_id = 3 as something

You can find more info here : You can't specify target table for update in FROM clause

Update Where Column not in Select

5 Answers5