2

i have an eav table structure, special_from, special_to and special_display are not required to be filled

i'm making an sql script that populates these eav attributes for products, how my script works is that the data for these 3 values is processed and stored in a temp table, when i have all the data that's when i need to update the eav attributes

it is possible for a products to have these values so i have 2 apposes

1) DELETE the eav values for the products i have listed in my temp table then INSERT them 2) INSERT the values that have not been assigned then UPDATE the ones that have

i'm working with up to 10,000 products, so i'm wondering which is more efficient since i want to minimize the amount of time the query takes

Memor-X
  • 2,870
  • 6
  • 33
  • 57

1 Answers1

1
INSERT INTO
  catalog_product_entity_datetime
  (entity_type_id, store_id, entity_id, attribute_id, value)
VALUES
  (4, 0, PRODUCT_ID_1, ATTRIBUTE_ID_FOR_SPECIAL_TO, SOME_DATE_VALUE),
  (4, 0, PRODUCT_ID_1, ATTRIBUTE_ID_FOR_SPECIAL_FROM, SOME_DATE_VALUE),
  (4, 0, PRODUCT_ID_2, ATTRIBUTE_ID_FOR_SPECIAL_TO, SOME_DATE_VALUE),
  (4, 0, PRODUCT_ID_2, ATTRIBUTE_ID_FOR_SPECIAL_FROM, SOME_DATE_VALUE),
  -- ... and so on
  -- ... and so on
  -- ... and so on
ON DUPLICATE KEY UPDATE value = VALUES(value)

And similar for the special display field (whichever datatype that may be.

I assumed magento here, since it is the only thing i ever saw using EAV.

With this 2 queries you would be able to update all affected products at once, thus minimizing the impact on the database (reindexing). Another way would be to do single queries within a loop within a transaction, to avoid a reindex per entry.

scones
  • 3,317
  • 23
  • 34