Полезные SQL запросы для Opencart

Мы собрали полезные SQL запросы для Opencart 2 и Opencart 3

Важно!!! Перед любым изменением в базе данных, необходимо делать бэкап базы данных!!! (как сделать бэкап базы данных- на каждом хостинге по своему).

1. SQL запрос для Opencart, что бы установить количество для всех товаров 0, либо другое кол-во

UPDATE `oc_product` SET `quantity` = 0

2. SQL запрос для Opencart, что бы удалить производителей не связанных с товарами

DELETE m
FROM oc_manufacturer m
LEFT JOIN oc_product p ON
m.manufacturer_id = p.manufacturer_id
WHERE p.manufacturer_id IS NULL

3. Установить количество бонусных баллов для покупки товара равное цене в Opencart

UPDATE `product` SET points = round(price);

4. Запрос на удаление всех акций с ценой = 0 в Opencart 3 и Opencart 2

DELETE FROM `oc_product_special` WHERE price = 0

5. Как изменить количество товара в существующих опциях в opencart:

UPDATE `oc_product_option_value` set `quantity` = 100

 - или с дополнительными условиями

фиолетовым добавил строчки с условиями-привязкой к id конкретной категории товаров, конкретных опций или конкретных значений опций. На случай, если Вам нужно эти изменения сделать только для товаров какой-то конкретной группы. если нужно для всех товаров - можете просто убрать фиолетовые строки или проигнорировать (сейчас они закомментированы)

100 зеленым - новое количество товара этой опции

0 зеленым - старое количество товара этой опции. изменится на 100.

UPDATE oc_product_option_value pov
LEFT JOIN oc_product_to_category p2c
  ON pov.product_id = p2c.product_id
LEFT JOIN oc_option o
  ON o.option_id = pov.product_option_id
LEFT JOIN oc_option_value ov
  ON ov.option_value_id = pov.option_value_id
SET pov.quantity = 100
WHERE pov.quantity = 0
-- AND p2c.category_id = 69
-- AND o.option_id = 14
-- AND ov.option_value_id = 51 

6. Как привязать все товары в мультимагазине для магазинов в Opencart. Вместо xx - id нужного магазина. Категории, производители.. по этому же принципу

INSERT IGNORE INTO `oc_product_to_store` (product_id, store_id) SELECT p.product_id, xx as store_id FROM `oc_product` p

7. Как отключить доставку для всех товаров в Opencart. Массово поменять значение параметра "Необходима доставка" на "Нет":

UPDATE oc_product SET shipping=0;

8. Как заполнить поле модель (код товара) для всех товаров в Opencart. Модель будет сформирована на основе id товаров с добавлением ведущих нулей до шестизначного вида:

UPDATE oc_product SET model = LPAD(product_id,6,'000000')

9. Как заполнить поле SKU (Артикул) для всех товаров в Opencart. Артикул будет сформирован на основе id товаров с добавлением ведущих нулей до шестизначного вида:

UPDATE oc_product SET sku = LPAD(product_id,6,'000000');

10. Массовое изменение цены в Opencart:

- этот пример разделит все цены на 75.99, так мы можем перевести цены в рублях на евро.

UPDATE `oc_product` SET `price` = `price` / 95.99;

- так мы сделаем те же действий, но для тех товаров, у которых цена больше 1. Аналогично, Вы можете добавить свои условия, по другим полям.

UPDATE `oc_product` SET `price` = `price` / 65.99 WHERE `oc_product`.`price` > 1;

- нужно увеличить цену на все товары в opencart на 7%. Т.е в рамках запроса, мы можем производить математические действия +-*/.

UPDATE `oc_product` SET `price` = `price` + (`price`/100*7) WHERE `oc_product`.`price` > 1;

- вычитаем 8% от товаров определенного производителя

UPDATE `oc_product` SET `price` = `price` - (`price`/100*8) WHERE `oc_product`.`manufacturer_id` = 54;

11. Отключает товары у которых цена = 0 в Opencart

UPDATE oc_product SET oc_product.status = 0 WHERE price = 0

12. Мультимагазин на opencart 3, скопировать seo url (сео чпу), для всех магазинов. Где 1 - это id магазина куда копируем, а нуль - id магазина по умолчанию (отметил зеленым цветом).

INSERT INTO `oc_seo_url`(`store_id`, `language_id`, `query`, `keyword`)
SELECT 1, temp.language_id, temp.query, temp.keyword
FROM oc_seo_url temp
WHERE  temp.store_id = 0

13. Массово отключить товары у которых нет изображения в opencart

UPDATE `oc_product` SET `status`=0 WHERE `image`='' OR `image`='no_image.png' OR `image`='no_image.jpg' OR `image` IS NULL;

Обратное действие, включаем товары, где есть изображение

UPDATE oc_product SET status = 1 WHERE status = 0 AND image > ''

14. Удалить все товары с количеством равным 0... opencart 2

DELETE p1,       p2,       p3,       p4,       p5,       p6,       p7,       p8,       p9,       p10,       p11,       p12,       p13,       p14,       p15,       p16FROM `oc_product` p1LEFT JOIN `oc_product_attribute` p2 ON (p2.product_id = p1.product_id)LEFT JOIN `oc_product_description` p3 ON (p3.product_id = p1.product_id)LEFT JOIN `oc_product_discount` p4 ON (p4.product_id = p1.product_id)LEFT JOIN `oc_product_filter` p5 ON (p5.product_id = p1.product_id)LEFT JOIN `oc_product_image` p6 ON (p6.product_id = p1.product_id)LEFT JOIN `oc_product_option` p7 ON (p7.product_id = p1.product_id)LEFT JOIN `oc_product_option_value` p8 ON (p8.product_id = p1.product_id)LEFT JOIN `oc_product_recurring` p9 ON (p9.product_id = p1.product_id)LEFT JOIN `oc_product_related` p10 ON (p10.product_id = p1.product_id)LEFT JOIN `oc_product_reward` p11 ON (p11.product_id = p1.product_id)LEFT JOIN `oc_product_special` p12 ON (p12.product_id = p1.product_id)LEFT JOIN `oc_product_to_category` p13 ON (p13.product_id = p1.product_id)LEFT JOIN `oc_product_to_download` p14 ON (p14.product_id = p1.product_id)LEFT JOIN `oc_product_to_layout` p15 ON (p15.product_id = p1.product_id)LEFT JOIN `oc_product_to_store` p16 ON (p16.product_id = p1.product_id)WHERE p1.quantity = 999

15. Сделать все опции не обязательными в опенкарт

update oc_product_option set required = '0'

16. Как отключить категории без товаров в opencart 3

UPDATE oc_category c 
LEFT JOIN oc_product_to_category pc ON c.category_id = pc.category_id
LEFT JOIN oc_product p ON pc.product_id = p.product_id
SET c.status = 0
WHERE pc.product_id IS NULL 
OR pc.product_id=p.product_id AND p.status = 0

17. Как удалить определенный атрибут из всех товаров в opencart - в скобках указываем id атрибута, через запятую

DELETE FROM oc_product_attribute WHERE attribute_id IN (1, 2, 3, 4, 5, 6)

18. Как отключить индексацию товаров у определенной категории в OcStore 3

UPDATE `oc_product` SET `noindex`= 0 WHERE product_id IN (SELECT product_id FROM `oc_product_to_category` WHERE category_id = 1)

19. Как массово включить индексацию у категорий в OcStore 3

UPDATE `oc_category` SET `noindex` = 0

20. Как в Opencart увеличить максимальное количество символов в метатеге description

ALTER TABLE oc_product_description MODIFY meta_description VARCHAR(1000);
ALTER TABLE oc_information_description MODIFY meta_description VARCHAR(1000);
ALTER TABLE oc_category_description MODIFY meta_description VARCHAR(1000);
ALTER TABLE oc_manufacturer_description MODIFY meta_description VARCHAR(1000);

21. Как в opencart через SQL запрос присвоить всем товарам НДС

UPDATE `oc_product` SET `tax_class_id`=10

22. Как массово изменить цену опций в Opencart

* уменьшить цену, например на 20%

UPDATE oc_product p 
INNER JOIN oc_product_option_value pov ON p.product_id = pov.product_id 
SET pov.price = pov.price - (pov.price * 0.20);

* увеличить цену, например на 20%

UPDATE `oc_product` p INNER JOIN `oc_product_option_value` pov ON p.`product_id` = pov.`product_id` SET pov.`price` = pov.`price` + (pov.`price`/100*20)

* увеличить цену у определенного производителя, например на 20%

UPDATE `oc_product` p INNER JOIN `oc_product_option_value` pov ON p.`product_id` = pov.`product_id` SET pov.`price` = pov.`price` + (pov.`price`/100*20) WHERE p.`manufacturer_id` = 111

* увеличить цену у перечисленных производителей

UPDATE `oc_product` p INNER JOIN `oc_product_option_value` pov ON p.`product_id` = pov.`product_id` SET pov.`price` = pov.`price` + (pov.`price`/100*10) WHERE p.`manufacturer_id` IN (111, 112, 113)

23. Как массово добавить к цене акции процент через SQL запрос в Opencart

update oc_product_special set price=price*1.07

24. Как массово скопировать из значений атрибута в поле location, upc, isbn... через SQL запрос в Opencart

*WHERE pa.attribute_id = 1; указываем id атрибута из которого переносим значения

UPDATE oc_product p
INNER JOIN oc_product_attribute pa ON p.product_id = pa.product_id
SET p.location = pa.text
WHERE pa.attribute_id = 26;

25. Как добавить товар во все родительские категории в OpenCart через SQL запрос

Иногда бывает, что каталог товаров уже заполнен, но каждый товар привязан только к одной категории нижнего уровня. Однако, чтобы товар отображался во всех родительских категориях, достаточно выполнить один запрос к базе данных MySQL.

Рассмотрим пример товара «Точечный светильник №1» и следующую иерархию категорий:

Светильники-Точечные светильники-Встроенные

Сейчас товар отображается только в категории «Встроенные», но мы хотим, чтобы он был доступен и в других связанных категориях. Если у нас много товаров и различных категорий с иерархиями, добавление товаров в родительские категории вручную становится трудоемким и времязатратным процессом.

делаем SQL запрос:

INSERT INTO oc_product_to_category (product_id, category_id)
SELECT p.product_id, c2.category_id
FROM oc_product p
INNER JOIN oc_product_to_category ptc ON p.product_id = ptc.product_id
LEFT JOIN oc_category_path cp ON ptc.category_id = cp.category_id
LEFT JOIN oc_category c1 ON cp.category_id = c1.category_id
LEFT JOIN oc_category c2 ON cp.path_id = c2.category_id
WHERE c1.category_id <> c2.category_id;

Для улучшения производительности запроса можно создать индексы на столбцах, которые используются в операторах JOIN и WHERE, например:

CREATE INDEX idx_product_id ON oc_product (product_id);
CREATE INDEX idx_category_id ON oc_product_to_category (category_id);
CREATE INDEX idx_path_id ON oc_category_path (path_id);
CREATE INDEX idx_category_id_2 ON oc_category (category_id);

Так же советуем прочитать запросы для массового удаления товаров, категорий, производителей, атрибутов, опций и очистки seo урл в Opencart 2 и Opencart 3