News:

Support the VirtueMart project and become a member

Main Menu

SQL Statement for Products

Started by arnold.pietersen, August 10, 2014, 14:40:58 PM

Previous topic - Next topic

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
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/