Update column with value from other column

Question

I wish to update special_price to existing price in magento 2. How do I do it?

price and special_price attribute both are stored in table catalog_product_entity_decimal with attribute_code 74 and 75.

catalog_product_entity_decimal table

Now I've to update 1349 (the value of entity_id=9773 and attribute_id=75) to 1619 (value of entity_id=9773 and attribute_id=74)

Similarly, want to update the value of entity_id=9774 and attribute_id=75 to 1619 from 1349.

Every entity may have different value for each attribute_id.

I need sql update statement which updates value of such 200,000 entities in the table at one go. This sql will update price and special_price to each other in one sql statement for all the products in magento 2.

I tried to run this sql but did not work:

UPDATE catalog_product_entity_decimal val
SET val.value = (SELECT value FROM catalog_product_entity_decimal WHERE attribute_id = '74' and entity_id='9773')
WHERE val.attribute_id = '75' and val.entity_id='9773'

The error says "select cannot be from same table"

You might as well just set all special price fields to empty then as setting to the same value as price will have the same effect, essentially disabling the feature — Andrew

ScaisEdge ScaisEdge · Accepted Answer · 2018-08-15T16:18:48

You can try with an inner join

  UPDATE catalog_product_entity_decimal val
  INNER JOIN catalog_product_entity_decimal val2 ON val.attribute_id = '75' 
        and val.entity_id ='9773'
          and val2.attribute_id = '74' and val2.entity_id='9773'
  SET val.value = val2.value

or an inner join based on subselect

  UPDATE catalog_product_entity_decimal val
  INNER JOIN ( select  value, attribute_id, entity_id 
    from  catalog_product_entity_decimal val2
    where  val21.attribute_id = '74' and val2.entity_id='9773'
  ) t ON val.attribute_id = '75' 
        and val.entity_id ='9773'
  SET val.value = t.value

for all the entity_id you maust use this column in the join condition

  UPDATE catalog_product_entity_decimal val
  INNER JOIN catalog_product_entity_decimal val2 ON val.attribute_id = '75' 
          and val2.attribute_id = '74' 
            and val2.attribute_id = val.attribute_id 
  SET val.value = val2.value

OR

 UPDATE catalog_product_entity_decimal val
  INNER JOIN ( select  value, attribute_id, entity_id 
    from  catalog_product_entity_decimal val2
    where  val21.attribute_id = '74' 
  ) t ON val.attribute_id = '75' 
        and val2.attribute_id = t.attribute_id 
  SET val.value = t.value

Update column with value from other column

1 Answers