[SOLVED] - SQL - Copy "name product" from product_lang to caption image?

[ietax]

Good evening,

I have to face the following problem:many of my products have an incorrect image description which have to be modified. On the basis of the huge quantity of products it seems impossible to corre everything manually

 

Is it possible to work directly in the db and to copy allthe the "name" fields of ps_product_lang tab in the matching fields of the image tab?ps_image_lang

 I manage to create following tab

 

 

SELECT l.*,p.* FROM `ps_image_lang` l

join `ps_image` i on i.`id_image` = l.`id_image`

join  `ps_product_lang` p on i.`id_product`=p.`id_product`

 

But I'm not able to substitute the fields... thanks

Edited March 23, 2017 by ietax (see edit history)

[zahiras]

This query works for me
 

UPDATE ps_image_lang AS l 

INNER JOIN ps_image AS i ON (i.id_image = l.id_image)

INNER JOIN ps_product_lang AS pl ON (pl.id_product = i.id_product)

SET l.legend = pl.name

WHERE l.id_lang = 2

AND pl.id_lang = 2

Edited February 24, 2017 by zahiras (see edit history)

[ietax]

I did not find any solution... are you expert with sql query?

[zahiras]

I'am not an expert, but the query I wrote before totally does the job (if you want to take the product name and replace image caption with it).
Is that what you want?

[ietax]

ok perfect, it works. thanks a lot

[Kaper]

Hello,

Thank guys, this is working:

UPDATE ps_image_lang AS l 
INNER JOIN ps_image AS i ON (i.id_image = l.id_image)
INNER JOIN ps_product_lang AS pl ON (pl.id_product = i.id_product)
SET l.legend = pl.name
WHERE l.id_lang = 2
AND pl.id_lang = 2

Please i Have a question, i would like to  moove this query to next level. Is it possible to copy Product name +assigned color combination to img caption name?
 

So for example if i had this product in my shop:

"Insane T-shirt" in 2 colors -> 1) Black and 2) White

The image caption will be: "Insane T-shirt Black" & "Insane T-shirt White" (ofcourse if you have assigned images to color combinations)

 

I know it would be quite complicated, but i don´t think that it is imimpossible

 

Is there any who can help us? Thanks youuu

 

 

[Kaper]

UP. Please if there is anybody, who can code script for that, i would pay for it. Thanks !

/edit: Solved... it took 2 minutes to my friend btw, so "thanks" for help ^^

Edited October 2, 2017 by Kaper (see edit history)

[flyclothing]

This worked for me. I changed database name to fit mine and language.

UPDATE
    ps2_image_lang AS l
INNER JOIN ps2_image AS i
ON
    (i.id_image = l.id_image)
INNER JOIN ps2_product_lang AS pl
ON
    (pl.id_product = i.id_product)
SET
    l.legend = pl.name
WHERE
    l.id_lang = 1 AND pl.id_lang = 1