MYSQL Query not changing, updating value from value in same column

Question

I asked this question on another post which have me a basic query but still not working when executing the following nothing happens any ideas?

UPDATE mage_catalog_product_entity_varchar 
  SET value = (SELECT value WHERE attribute_id = 703) 
  WHERE attribute_id = 106;

So I am updating the value in the value column if the attribute_id column = 106 but only updating from the value column where the attribute_id column = 703

The following query also updates 0 in the value fields corresponding to attribute_id 106

UPDATE mage_catalog_product_entity_varchar 
  SET value = (SELECT value from mage_catalog_product_entity_varchar 
  WHERE attribute_id = 703) WHERE attribute_id = 106;

Doesn't the inner `select` need a `FROM` clause? Or am I missing something? — Aleks G, Oct 26 '12 at 15:48

score 1 · Accepted Answer · edited May 23 '17 at 12:21

You are missing the From clause in the sub query. Can you recheck it once

UPDATE mage_catalog_product_entity_varchar 
  SET value = (SELECT value WHERE attribute_id = 703) 
  WHERE attribute_id = 106;

It should be (MYSQL doesn't updates the table if it is used in the inner query directly)

UPDATE mage_catalog_product_entity_varchar a, 
   mage_catalog_product_entity_varchar b
    SET a.value = b.value WHERE a.attribute_id = 106
     AND b.attribute_id = 703;

You can also find the solution on SQL Fiddle 1

MySQL gives the error "You can't specify target table 'table_name' for update in FROM clause" For knowing more about this error, you can visit the topic

MySQL #1093 - You can't specify target table 'giveaways' for update in FROM clause

If you will have more than one entries in the table discussed corresponding to attribute id 703, then the query will take value from the first record inserted and the other one will be ignored.

You can see this condition here SQL Fiddle 2

The only issue is you cant select from inside the brackets – Simon Staton Oct 26 '12 at 15:50 — Simon Staton, Oct 26 '12 at 15:50
I have modified my solution. Please check now. – Vivek Oct 26 '12 at 16:22 — Vivek, Oct 26 '12 at 16:22

score 0 · Answer 2 · answered Oct 26 '12 at 16:14

You could also do it as a correlated update without sub-select such as

UPDATE mage_catalog_product_entity_varchar, 
       mage_catalog_product_entity_varchar WantFromHere
   SET value = WantFromHere.value
   WHERE attribute_id = 106
     AND WantFromHere.attribute_id = 703;

But, since your "other" table is for a specific attribute is not really a "JOIN", the where clause I've just applied the specific attribute ID you want it from (ie: 703). I don't know how many records are involved, but it DOES prevent the "subquery" for every record to be processed.

MYSQL Query not changing, updating value from value in same column

2 Answers2