Reference code at the and of all product name

Question

I have a table ps_product with:

ps_product

-------------------------
|id_product |reference  |
-------------------------
|1          |c11111     |
|2          |x222222    |
|3          |x222222    |
-------------------------

and a table ps_product_lang with:

ps_product_lang

---------------------------------
|id_product |name               |
---------------------------------
|1          |Disco 1TB          |
|2          |Disco x222222 2TB  |
|3          |Disco 3TB x3333    |
---------------------------------

Is there a MySQL script to run in PhPMyAdmin to add reference code at the and of all product name ?

The result should be:

ps_product_lang

---------------------------------
|id_product |name               |
---------------------------------
|1          |Disco 1TB c11111   |
|2          |Disco 2TB x222222  |
|3          |Disco 3TB x3333    |
---------------------------------

Thanks

https://stackoverflow.com/a/1262848/3270427 – McNets Apr 11 '18 at 13:20 — McNets, Apr 11 '18 at 13:20
Is DISCO and TB are common keywords in the entire table? – jose_bacoy Apr 11 '18 at 13:22 — jose_bacoy, Apr 11 '18 at 13:22

score 1 · Accepted Answer · answered Apr 11 '18 at 13:29

1

UPDATE `ps_product_lang` pl
LEFT JOIN `ps_product` p
    ON p.`id_product` = pl.`id_product`
SET pl.`name` = REPLACE(CONCAT(REPLACE(pl.`name`, p.`reference`, ""), " ",p.`reference`), "  ", " ");

First we check if the reference exists inside the old name and remove it

REPLACE(pl.`name`, p.`reference`, "")

Then we add the reference at the end with a space

CONCAT(REPLACE(pl.`name`, p.`reference`, ""), " ",p.`reference`)

Finally we remove unwanted double spaces due to the removing of the reference in the old name field.

REPLACE(CONCAT(REPLACE(pl.`name`, p.`reference`, ""), " ",p.`reference`), "  ", " ")

answered Apr 11 '18 at 13:29

Florian Lemaitre

5,905
2
21
44

I get the message: /* Errore SQL (1406): Data too long for column 'name' at row 1 */ – Mirco Apr 11 '18 at 13:56
The message is explicit, some references and names are too long to be concatenated together, due to the limit of 256 characters on the name field. – Florian Lemaitre Apr 11 '18 at 14:05
1

I have solved by modifying my.ini sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" – Mirco Apr 11 '18 at 14:19

ernesthm · Answer 2 · 2018-04-11T14:45:01.257

0

I think this way is more natural:

UPDATE ps_product_lang pl, ps_product p SET pl.name = CONCAT(RTRIM(REPLACE(pl.name, p.reference, '')), ' ', p.reference) WHERE p.id_product = pl.id_product

edited Apr 11 '18 at 14:45

answered Apr 11 '18 at 14:13

ernesthm

421
4
15

it does not check if the reference code is already included in the name – Mirco Apr 11 '18 at 14:28

Reference code at the and of all product name

2 Answers2