2022-04-24
20002
70
6781
2 minutes.


SQL queries for individual updating of the online store website

SQL queries allow you to update the website of the online store according to your individual requirements. Each CMS needs its own SQL queries, so in this section there will be separate queries for each CMS. Queries are written in the section "Configuring SQL queries"
elbuz etrade jumper export products

CMS OpenCart

The examples are given with the table prefix "oc_", if you have a different prefix, change it in the query.

Updating the title meta tag for the OpenCart product manufacturer based on the title tag from the E-Trade Jumper manufacturers directory

UPDATE oc_manufacturer_description md INNER JOIN etrade_manufacturer_temp t2 ON md. manufacturer_id = t2. manufacturer_id SET md. meta_title = t2. meta_title;;;

Updating the OpenCart product model based on the E-Trade Jumper product ID
UPDATE oc_product t1, etrade_product_temp t2 SET t1. model = t2. product_id_jumper WHERE t2. product_id>0 AND t2. product_id = t1. product_id;;;

OpenCart product description update based on E-Trade Jumper short description

UPDATE oc_product_description t1, etrade_product_temp t2 SET t1. description = t2. description_short WHERE t1. product_id = t2. product_id AND t1. language_id = t2. language_id;;;

SEO URL Update OpenCart Based on SEO E-Trade Jumper
When updating the OpenCart site, CNCs are registered only for new entries (new categories, products), to update the current CNCs on the site, use these SQL queries.

OpenCart version 2. X
# Categories
UPDATE oc_url_alias ua INNER JOIN etrade_category_temp e ON e. category_id = CAST(SUBSTRING(ua. query FROM 13) as UNSIGNED) SET ua. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND ua. query LIKE 'category_id=%';;

# Products
UPDATE oc_url_alias ua INNER JOIN etrade_product_temp e ON e. product_id = CAST(SUBSTRING(ua. query FROM 12) as UNSIGNED) SET ua. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND ua. query LIKE 'product_id=%';;

# Manufacturers
UPDATE oc_url_alias ua INNER JOIN etrade_manufacturer_temp e ON e. manufacturer_id = CAST(SUBSTRING(ua. query FROM 17) as UNSIGNED) SET ua. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND ua. query LIKE 'manufacturer_id=%';;

OpenCart version 3. X
# Categories
UPDATE oc_seo_url su INNER JOIN etrade_category_temp e ON e. category_id = CAST(SUBSTRING(su. query FROM 13) as UNSIGNED) SET su. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND su. query LIKE 'category_id=%';;

# Products
UPDATE oc_seo_url su INNER JOIN etrade_product_temp e ON e. product_id = CAST(SUBSTRING(su. query FROM 12) as UNSIGNED) SET su. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND su. query LIKE 'product_id=%';;

# Manufacturers
UPDATE oc_seo_url su INNER JOIN etrade_manufacturer_temp e ON e. manufacturer_id = CAST(SUBSTRING(su. query FROM 17) as UNSIGNED) SET su. keyword = e. seo_url WHERE e. row_exist = 1 AND e. seo_url!='' AND su. query LIKE 'manufacturer_id=%';;

Update on the OpenCart website of the minimum number of products to order
UPDATE etrade_product_temp t1, oc_product t2 SET t2. minimum = t1. order_minimum WHERE t1. uuid = t2. uuid;;;

Updating prices for OpenCart product options based on option products created in the E-Trade Jumper base catalog
To update option prices on the site, you must activate the flag "Update option values based on option products"
elbuz etrade jumper export products
Updating the price, price prefix, article for an option product
UPDATE oc_product_option_value pov INNER JOIN etrade_product_attribute_temp pa ON pa. product_id = pov. product_id AND pov. product_option_id = pa. product_option_id AND pov. option_id = pa. option_id AND pov. product_option_value_id = pa. product_option_value_id INNER JOIN etrade_product_temp p ON p. uuid = pa. product_uuid AND p. type_id=2 SET pov. price = p. price, pov. price_prefix = '=', pov. sku = p. mpn;;;
You can update the price difference between the main product and the option product for option products, if each option has its own price
UPDATE oc_product_option_value pov INNER JOIN etrade_product_attribute_temp pa ON pa. product_id = pov. product_id AND pov. product_option_id = pa. product_option_id AND pov. option_id = pa. option_id AND pov. option_value_id = pa. product_option_value_id INNER JOIN (SELECT uuid, price FROM etrade_product_temp WHERE type_id=1) p ON p. uuid = pa. product_uuid INNER JOIN (SELECT uuid_parent, price FROM etrade_product_temp WHERE type_id=2) p_option ON p_option. uuid_parent = p. uuid SET pov. price = p_option. price - p. price;;;

CMS 1C-Bitrix

Bitrix property update based on the usual E-Trade Jumper field.
For example, to update the Bitrix property with the SUPPLIER code for the ID = 2 infoblock based on the "Accountant name" field, you must use these SQL queries

SET @iblock_id = 2;;; SET @property_code = 'SUPPLIER';; SET @property_id = (SELECT b_iblock_property. id FROM b_iblock_property WHERE b_iblock_property. code = @property_code AND b_iblock_property. IBLOCK_ID = @iblock_id LIMIT 1);;; INSERT INTO b_iblock_element_property (iblock_property_id, iblock_element_id, value) SELECT @property_id, b_iblock_element. id, etrade_product_temp. contractor_name FROM b_iblock_element, etrade_product_temp WHERE b_iblock_element.xml_id = etrade_product_temp. uuid AND b_iblock_element. iblock_id = @iblock_id AND b_iblock_element. id NOT IN (SELECT iblock_element_id FROM b_iblock_element_property WHERE iblock_property_id = @property_id GROUP BY iblock_element_id);;; UPDATE b_iblock_element_property, b_iblock_element, etrade_product_temp SET b_iblock_element_property. value = etrade_product_temp. contractor_name WHERE b_iblock_element. id = b_iblock_element_property. iblock_element_id AND b_iblock_element.xml_id = etrade_product_temp. uuid AND b_iblock_element. iblock_id = @iblock_id AND b_iblock_element_property. iblock_property_id = @property_id;;;


Program for online store


Copywriter ElbuzGroup


Contents:



Save a link to this article