Skip to content

Instantly share code, notes, and snippets.

@debuss

debuss/prestashop_create_references.sql

Created April 25, 2018 02:26
Show Gist options
  • Save debuss/87608854dc54372d7227d9ff5715e847 to your computer and use it in GitHub Desktop.
Save debuss/87608854dc54372d7227d9ff5715e847 to your computer and use it in GitHub Desktop.
Download ZIP
This query generate references (SKU) for your products and combinations on PrestaShop.
Raw
prestashop_create_references.sql
-- Set references on parent products
UPDATE `ps_product` p
SET p.`reference` = REPLACE(REPLACE(LOWER(CONCAT_WS(
"_",
LEFT((SELECT CONVERT(`name` USING ascii) FROM `ps_product_lang` pl WHERE pl.`id_product` = p.`id_product` LIMIT 1), 10),
p.`id_product`,
"0"
)), "?", ""), " ", "")
WHERE p.`reference` IS NULL
OR p.`reference` = "";
-- Set references on child products
UPDATE `ps_product_attribute` pa
SET pa.`reference` = REPLACE(REPLACE(LOWER(CONCAT_WS(
"_",
LEFT((SELECT CONVERT(`name` USING ascii) FROM `ps_product_lang` pl WHERE pl.`id_product` = pa.`id_product` LIMIT 1), 10),
pa.`id_product`,
pa.`id_product_attribute`
)), "?", ""), " ", "")
WHERE pa.`reference` IS NULL
OR pa.`reference` = "";
@ilario-santa
Copy link

hi

Hi
I was studying your query, could you give me a hand to modify it, so that the reference is thus "ProductName-Nomeattribute"

best regards
Ilario

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