SQL Statement for Products

[arnold.pietersen]

Hi

I have migrated a VirtueMart 1 installation to VirtueMart 2. I seem to have a problem with the images. It appears that the on the frontend, the images have .png extensions. However, on the backend it has .jpg extensions. What we discovered is that once we have attached an image to a product that the j25_vm_product_medias table gets populated with the product_id and the media_id.

I have now written a SQL statement to speed up the process.

In short the SQL: statement must do the following

1. Update Table 1 Column 1 with the value of Table 2 Column 1
2. Update Table 1 Column 2 with the value of Table 3 Column 1
3. Update must be based on the fact that a value in Table 2 Column 2 (e.g. 62-004) is in Table 3 Column 2 (62-004.png)

I used the following SQL statement, which does not appear to work, apparently there is an unknown column:

UPDATE t1 [
Set t1.c1 = t2.c1, t1.c2 = t3.c1;
Where t2.c2 in
(select c2
from t3)

The longer version is:
UPDATE j25_virtuemart_product_medias
Set j25_virtuemart_product_medias.virtuemart_product_id = j25_virtuemart_products.virtuemart_product_id, j25_virtuemart_product_medias.virtuemart_media_id = j25_virtuemart_medias.virtuemart_media_id;
Where j25_virtuemart_products.product_sku in
(select file_title
from j25_virtuemart_medias)

Any guidance will be appreciated.

Kind Regards

ARNOLD

[Milbo]

The native migrator and the migrator of daycounts are doing this for you, imho