Skip to content

Instantly share code, notes, and snippets.

@JosephMaxwell

JosephMaxwell/script.sql

Created November 7, 2017 22:03
Show Gist options
  • Save JosephMaxwell/5aa4495555f4711d64e2e1a23a4362cd to your computer and use it in GitHub Desktop.
Save JosephMaxwell/5aa4495555f4711d64e2e1a23a4362cd to your computer and use it in GitHub Desktop.
Download ZIP
Finding matching Row IDs in the entity tables (EE)
Raw
script.sql
SELECT "Value ID", "Attribute ID", "Store ID", "Row ID", "Value", "Value ID", "Attribute ID", "Store ID", "Row ID", "Value"
UNION ALL
SELECT 0, 0, 0, 0, "TEXT TABLE" as value, 0, 0, 0, 0, ""
UNION ALL
SELECT * FROM catalog_product_entity_text `store0` INNER JOIN catalog_product_entity_text `store1` ON `store1`.attribute_id = `store0`.attribute_id AND `store0`.row_id = `store1`.row_id AND `store1`.store_id = 1 WHERE `store0`.store_id = 0 AND `store0`.value != `store1`.value
UNION ALL
SELECT 0, 0, 0, 0, "INT TABLE" as value, 0, 0, 0, 0, ""
UNION ALL
SELECT * FROM catalog_product_entity_int `store0` INNER JOIN catalog_product_entity_int `store1` ON `store1`.attribute_id = `store0`.attribute_id AND `store0`.row_id = `store1`.row_id AND `store1`.store_id = 1 WHERE `store0`.store_id = 0 AND `store0`.value != `store1`.value
UNION ALL
SELECT 0, 0, 0, 0, "DECIMAL TABLE" as value, 0, 0, 0, 0, ""
UNION ALL
SELECT * FROM catalog_product_entity_decimal `store0` INNER JOIN catalog_product_entity_decimal `store1` ON `store1`.attribute_id = `store0`.attribute_id AND `store0`.row_id = `store1`.row_id AND `store1`.store_id = 1 WHERE `store0`.store_id = 0 AND `store0`.value != `store1`.value
UNION ALL
SELECT 0, 0, 0, 0, "VARCHAR TABLE" as value, 0, 0, 0, 0, ""
UNION ALL
SELECT * FROM catalog_product_entity_varchar `store0` INNER JOIN catalog_product_entity_varchar `store1` ON `store1`.attribute_id = `store0`.attribute_id AND `store0`.row_id = `store1`.row_id AND `store1`.store_id = 1 WHERE `store0`.store_id = 0 AND `store0`.value != `store1`.value;
@JosephMaxwell
Copy link
Author

If you need to then copy the values from store 1 to store 0:

UPDATE catalog_product_entity_int `store0` INNER JOIN catalog_product_entity_int `store1` ON `store1`.attribute_id = `store0`.attribute_id AND `store0`.row_id = `store1`.row_id AND `store1`.store_id = 1
SET store0.value = store1.value
WHERE `store0`.store_id = 0 AND `store0`.value != `store1`.value

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment