I'm trying to update rows in MySQL from SQL view created with aliases. I do not know if this even is possible.

The original SQL looks like below. This I have saved as a view "ProductVersion".

SELECT e.entity_id AS id,
       v1.value AS name,
       e.sku,
       d1.value AS version
FROM mguu_catalog_product_entity e
LEFT JOIN mguu_catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM mguu_eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM mguu_eav_entity_type
        WHERE entity_type_code = 'catalog_product'))

LEFT JOIN mguu_catalog_product_entity_varchar d1 ON e.entity_id = d1.entity_id
AND d1.attribute_id = 171;

Now I would like to perform a MySQL UPDATE from that view, so I tried:

mysqli_query($db, "UPDATE ProductVersion SET version = '123123123' WHERE sku = '1003'");

This does not return any error - however it does not update though. I have also tried like this, but this creates an error:

mysqli_query($db, "UPDATE ProductVersion SET d1.value = '123123123' WHERE e.sku = '1003'");

Is it possible to do an MySQL UPDATE from a view and if not, how would it be done the easiest from the original SQL?

Thanks in advance.

1 Answers

0
Shim-Sao On

I don't know if that solve the problem but I think you can simplify your View something like this (less useless select statement) :

SELECT e.entity_id AS id,
   v1.value AS name,
   e.sku as sku,
   d1.value AS version
FROM mguu_catalog_product_entity e
LEFT JOIN mguu_catalog_product_entity_varchar v1 ON (e.entity_id = v1.entity_id)
LEFT JOIN mguu_catalog_product_entity_varchar d1 ON (e.entity_id = d1.entity_id)
LEFT JOIN mguu_eav_attribute AS mea ON (v1.attribute_id = mea.attribute_id)
LEFT JOIN mguu_eav_entity_type AS meet ON (meet.entity_type_code = 'catalog_product')
WHERE v1.store_id = 0
AND mea.attribute_code = 'name'
AND mea.entity_type_id = meet.entity_type_id
AND d1.attribute_id = 171;

In MySql documentation :

UPDATE: The table or tables to be updated in an UPDATE statement may be view references that are merged. If a view is a join view, at least one component of the view must be updatable (this differs from INSERT).

In a multiple-table UPDATE statement, the updated table references of the statement must be base tables or updatable view references. Nonupdated table references may be materialized views or derived tables.

https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html