Useful MySQL Queries for Working With the OpenCart Database

If you administer an OpenCart store, sooner or later the need arises to mass-change or retrieve something from the database. We offer a selection of useful MySQL queries for OpenCart.

1. Find and replace a specific phrase in product names:

UPDATE `oc_product_description` SET name = REPLACE(name, 'старий текст', 'новий текст');

2. Find and replace a specific phrase in product descriptions:

UPDATE `oc_product_description` SET description = REPLACE(description, 'старий текст', 'новий текст');

3. Replace an attribute in products (for merging identical attributes into one and removing duplicates):

UPDATE `oc_product_attribute` SET `attribute_id`='1' WHERE `attribute_id`='2';

4. Find all duplicate SEO URLs

SELECT keyword, COUNT(*) counter FROM `oc_seo_url` GROUP BY keyword HAVING counter > 1;

5. Find products with identical names

SELECT name, COUNT(*) counter FROM `oc_product_description` GROUP BY name HAVING counter > 1;

6. Get the category IDs of a product

SELECT category_id FROM `oc_product_to_category` WHERE product_id = '99'

7. Get the main category ID of a product (for ocStore or when SeoPro is installed)

SELECT category_id FROM `oc_product_to_category` WHERE product_id = '99' AND main_category = '1'

8. Update all prices, multiply by a factor of 1.2

UPDATE `oc_product` SET price = (price * 1.2)

9. Restore access to the OpenCart admin - create a new administrator with the login "opencart" and the password "opencart"

INSERT INTO `oc_user` (`user_group_id`, `username`, `password`, `salt`, `firstname`, `lastname`, `email`, `image`, `code`, `ip`, `status`, `date_added`) VALUES ( '1', 'opencart', '4e8e674a6e2060d38cdda9e614949901e0ff4073', 'wxhCo24Fv', 'Firstname', 'Lastname', 'opencart@opencart.com', '', '', '', '1', '2020-01-01 10:00:00');

10. Reset the administrator password for the login "admin" - set its password to "opencart"

UPDATE `oc_user` SET `password` = '4e8e674a6e2060d38cdda9e614949901e0ff4073', `salt` = 'wxhCo24Fv' WHERE username = 'admin'

11. Append the store name to the end of the product Meta Title

UPDATE `oc_product_description` SET meta_title = CONCAT(meta_title, " - My Store")

12. Delete categories without names and descriptions

DELETE c FROM `oc_category` AS c LEFT JOIN `oc_category_description` AS cd USING (category_id) WHERE cd.category_id IS NULL;

13. Mass-delete an attribute from all products and remove the attribute itself from the system

SET @attribute_id = 123; 
DELETE FROM `oc_product_attribute` WHERE `attribute_id` = @attribute_id; 
DELETE FROM `oc_attribute_description` WHERE `attribute_id` = @attribute_id; 
DELETE FROM `oc_attribute` WHERE `attribute_id` = @attribute_id;

14. Copy data from one table into another table.

INSERT INTO `oc_manufacturer_description` (manufacturer_id, meta_h1) SELECT manufacturer_id, name AS meta_h1 FROM oc_manufacturer;

This query will copy data from oc_manufacturer into the oc_manufacturer_description table.

15. Capitalize the first letter of all attribute values in products:

UPDATE `oc_product_attribute` SET text = CONCAT(UCASE(LEFT(text, 1)), SUBSTRING(text, 2));

16. Mass-replace an attribute value across products:

UPDATE `oc_product_attribute` SET text = 'New value' WHERE text = 'Old value';

17. Delete a country and its regions in OpenCart:

DELETE FROM oc_zone WHERE country_id = 176;
DELETE FROM oc_country WHERE country_id = 176;

18. Mass-delete countries and their regions, except for the specified country IDs:

DELETE FROM oc_zone WHERE country_id NOT IN ('67','117','123','170');
DELETE FROM oc_country WHERE country_id NOT IN ('67','117','123','170');

19. Reset AUTO_INCREMENT keys for the main DB tables after removing demo data:

ALTER TABLE oc_category AUTO_INCREMENT = 1;
ALTER TABLE oc_manufacturer AUTO_INCREMENT = 1;
ALTER TABLE oc_product AUTO_INCREMENT = 1;
ALTER TABLE oc_attribute AUTO_INCREMENT = 1;
ALTER TABLE oc_option AUTO_INCREMENT = 1;
ALTER TABLE oc_order AUTO_INCREMENT = 1;

This article will be supplemented with useful MySQL queries for working with OpenCart database tables