SQL Tips

Sometimes you need to change a lot of data at once and it would be very difficult to change it manually. In such a case it is advisable to work directly in the database and batch modify the data directly using SQL queries.

Be careful, the use of SQL queries is intended for experts only. Executing such queries can change a lot of data at once and there is no *undo* button. These queries should by usually executed on test servers, and a backup of the data is always required before running these queries.

Change the jos_ prefix to the prefix you are using.

Add tags to all products from selected category

-- add tags to all products from selected category (a.id ... product, 4 ... tag ID, c.id ... category ID)
REPLACE into jos_phocacart_tags_related (item_id, tag_id)
SELECT a.id, 4
FROM jos_phocacart_products a
LEFT JOIN jos_phocacart_product_categories AS pc ON pc.product_id = a.id
LEFT JOIN jos_phocacart_categories AS c ON c.id = pc.category_id
WHERE c.id = 1

Zero all of the stock (products)

-- zero all of the stock (products)
UPDATE jos_phocacart_products SET stock = '0';

Zero all of the stock (attributes)

-- zero all of the stock (attributes)
UPDATE jos_phocacart_attribute_values SET stock = '0';

Zero all of the stock (advanced stock management)

-- zero all of the stock (advanced stock management)
UPDATE jos_phocacart_product_stock SET stock = '0';

Set stock of selected products (based on ID)

-- set stock of selected products (based on ID)
UPDATE jos_phocacart_products SET stock = '10000' WHERE id > 0 AND id < 5;