Update multiple tables when using a join Max

Here is the query I have to update pricing and updated_at field for a product on my Magento 2.0 mySQL database.

SET @etype = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product');
SET @price  = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = @etype);

-- Admin store ID
SET @store = 0;


UPDATE catalog_product_entity_decimal d1

-- SELECT d1.row_id, d1.value AS 'Current', ip.sku, ip.price AS 'Updated', e.updated_at FROM catalog_product_entity_decimal d1
  JOIN (SELECT MAX(row_id) AS rowid, sku, updated_at FROM catalog_product_entity GROUP BY entity_id) e ON (d1.row_id = e.rowid)
  JOIN import_price ip ON e.sku = ip.sku
      AND d1.store_id = @store
      AND d1.attribute_id = @price
      
SET d1.value = ip.price, e.updated_at = NOW()

I am getting the error The target table e of the UPDATE is not updatable

I understand why I am getting the error, but I don't know how to fix.

If I remove the , e.updated_at = NOW() the query works fine.

Here is the table structure

CREATE TABLE prod23.catalog_product_entity (
  row_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Version Id',
  entity_id int(10) UNSIGNED NOT NULL COMMENT 'Entity Id',
  created_in bigint(20) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Update Id',
  updated_in bigint(20) UNSIGNED NOT NULL DEFAULT 2147483647 COMMENT 'Next Update Id',
  attribute_set_id smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
  type_id varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
  sku varchar(64) DEFAULT NULL COMMENT 'SKU',
  has_options smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
  required_options smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Required Options',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  PRIMARY KEY (row_id)
)

Solution

I assume your goal is to update only the rows in catalog_product_entity that have the greatest rowid for each entity_id, and leave other rows alone.

UPDATE catalog_product_entity_decimal d1
  JOIN catalog_product_entity e ON d1.row_id = e.row_id
  LEFT OUTER JOIN catalog_product_entity e2 
      ON e2.entity_id = e.entity_id AND e2.row_id > e.row_id
  JOIN import_price ip ON e.sku = ip.sku
      AND d1.store_id = @store
      AND d1.attribute_id = @price
SET d1.value = ip.price, e.updated_at = NOW()
WHERE e2.row_id IS NULL;

The purpose of the join to e2 is to find another row that has the same entity_id but a greater row_id. The condition WHERE e2.row_id IS NULL mean that we only want cases where there is no row e2 with a greater row_id than the id of e. That will naturally mean that e is the row with the greatest row_id for its respective entity_id.