Copy value to another field where columns value equals

Question

I am trying to copy one value in a column to another value in the same column by using another columns value for example:

column 1 - column 2
703 - /2/3/image.jpg
106 - "empty"
100 - description

So I want it to copy the value of column 2 where the value of column 1 = 706 and put into column 2 where value of column 1 = 106 so leaving the description bit fine. Does anyone know a query to do this?

Thanks, Simon

NOTE:

getting the following error while doing your suggestions

1093 - You can't specify target table 'mage_catalog_product_entity_varchar' for update in FROM clause

Thankyou for replys again: Need to add in INNER JOIN not to sure on how to do this method though any advice appreciated again, thanks

I removed the PHP tag since this is not php. If you feel you would accept a solution in PHP then maybe alter your question. — Anthony Hatzopoulos, Oct 26 '12 at 15:23

score 0 · Answer 1 · edited May 23 '17 at 12:03

0

~~You could do it using a subquery, like so:~~

update tablename set column2 = (select column2 from tablename where column1 = 706 limit 1) where column1 = 106;

EDIT
As it turns out, you can't have a subquery that references the table you are updating. As such, you need to use INNER JOIN (see the answer here: https://stackoverflow.com/a/4268431/1095946).

Try something like this:

UPDATE mage_catalog_product_entity_varchar t1, mage_catalog_product_entity_varchar t2
SET t1.value = t2.value
WHERE t1.attribute_id = 106
  AND t2.attribute_id = 703

edited May 23 '17 at 12:03

Community

1
1

answered Oct 26 '12 at 15:08

adamdunson

2,635
1
23
27

Thankyou, any ideas who to write the inner join part in? Thanks again – Simon Staton Oct 26 '12 at 15:18
Salil's answer seems to be the correct way, according to http://dev.mysql.com/doc/refman/5.0/en/update.html (search for "inner join"). – adamdunson Oct 26 '12 at 15:24
I see, any ideas on how to implement. Sorry my MYSQL is terrible I am just a designer but I do have some idea on what I am doing :) have you got the full query I could execute? – Simon Staton Oct 26 '12 at 15:26
Have tryed the following: Update mage_catalog_product_entity_varchar SET value = value WHERE attribute_id = 106 AND attribute_id = 703 – Simon Staton Oct 26 '12 at 15:28

score 0 · Answer 2 · answered Oct 26 '12 at 15:08

0

Update table t1, table t2 
SET t1.column2 = t2.column2 
WHERE t1.column1 = 106 AND t2.column1 = 706

answered Oct 26 '12 at 15:08

Salil

46,566
21
122
156

Thankyou for the reply Salil, any ideas on what the query should look like? – Simon Staton Oct 26 '12 at 15:30
trying this dosnt update yet: Update mage_catalog_product_entity_varchar t1, mage_catalog_product_entity_varchar t2 SET t1.value = t2.value WHERE t1.attribute_id = 106 AND t2.attribute_id = 703 – Simon Staton Oct 26 '12 at 15:31

Copy value to another field where columns value equals

1093 - You can't specify target table 'mage_catalog_product_entity_varchar' for update in FROM clause

2 Answers2