It is not a good practice, to delete orders completely. However it does not hurt much if taken with caution.

MAKE SURE TO HAVE A BACKUP OF YOUR DATABSE BEFORE TRYING THEM FOR A FIRST TIME. NO PROMISES OR GUARANTEES.

TO clear viewed statistics and all of your test attempts run this query on the database -

TRUNCATE `SC_aff_commissions`;
TRUNCATE `SC_aff_payments`;
TRUNCATE `SC_customer_addresses`;
TRUNCATE `SC_customer_reg_fields_values`;
TRUNCATE `SC_customer_reg_fields_values_quickreg`;
TRUNCATE `SC_customers`;
TRUNCATE `SC_order_status_changelog`;
TRUNCATE `SC_ordered_carts`;
TRUNCATE `SC_orders_discount_coupons`;
TRUNCATE `SC_shopping_cart_items`;
TRUNCATE `SC_shopping_cart_items_content`;
TRUNCATE `SC_shopping_carts`;
TRUNCATE `SC_orders`;
UPDATE `SC_categories` SET `viewed_times`=0;
UPDATE `SC_products`  SET `viewed_times`=0, `customers_rating`=0, `customer_votes`=0, `items_sold'=0

This is to delete orders info completely but to keep customer info -

TRUNCATE `SC_order_status_changelog`;
TRUNCATE `SC_ordered_carts`;
TRUNCATE `SC_orders_discount_coupons`;
TRUNCATE `SC_shopping_cart_items`;
TRUNCATE `SC_shopping_cart_items_content`;
TRUNCATE `SC_shopping_carts`;
TRUNCATE `SC_orders`;

This is to delete orders with the certain status code (cancelled is "1" by default, but do check and edit queries id necessary) -

DELETE SC_aff_commissions FROM SC_aff_commissions LEFT JOIN SC_orders ON SC_aff_commissions.orderID=SC_orders.orderID WHERE  SC_orders.statusID=1;
DELETE SC_customer_reg_fields_values_quickreg FROM SC_customer_reg_fields_values_quickreg LEFT JOIN SC_orders ON SC_customer_reg_fields_values_quickreg.orderID=SC_orders.orderID WHERE  SC_orders.statusID=1;
DELETE SC_order_status_changelog FROM SC_order_status_changelog LEFT JOIN SC_orders ON SC_order_status_changelog.orderID=SC_orders.orderID WHERE  SC_orders.statusID=1;
DELETE SC_ordered_carts FROM SC_ordered_carts LEFT JOIN SC_orders ON SC_ordered_carts.orderID=SC_orders.orderID WHERE  SC_orders.statusID=1;
DELETE FROM `SC_orders` WHERE statusID=1;

I always have a lot demo order to cancel.
What is the exactly procedure to delete cancelled orders ?
I dont know how to "run this query to a database"

Thanks

Last edited by wiz (2009-03-08 11:56)

This works fine for me:

TRUNCATE `SC_order_status_changelog`;
TRUNCATE `SC_ordered_carts`;
TRUNCATE `SC_orders_discount_coupons`;
TRUNCATE `SC_shopping_cart_items`;
TRUNCATE `SC_shopping_cart_items_content`;
TRUNCATE `SC_shopping_carts`;
TRUNCATE `SC_orders`;

But this report me a syntax error in the last line:

TRUNCATE `SC_aff_commissions`;
TRUNCATE `SC_aff_payments`;
TRUNCATE `SC_customer_addresses`;
TRUNCATE `SC_customer_reg_fields_values`;
TRUNCATE `SC_customer_reg_fields_values_quickreg`;
TRUNCATE `SC_customers`;
TRUNCATE `SC_order_status_changelog`;
TRUNCATE `SC_ordered_carts`;
TRUNCATE `SC_orders_discount_coupons`;
TRUNCATE `SC_shopping_cart_items`;
TRUNCATE `SC_shopping_cart_items_content`;
TRUNCATE `SC_shopping_carts`;
TRUNCATE `SC_orders`;
UPDATE `SC_categories` SET `viewed_times`=0;
UPDATE `SC_products`  SET `viewed_times`=0, `customers_rating`=0, `customer_votes`=0, `items_sol

Can you check if is correct ?

Thanks

Last edited by wiz (2009-03-21 17:06)

Alexey wrote:

At the very end you have a letter and some other things missing. It should be -

UPDATE `SC_products`  SET `viewed_times`=0, `customers_rating`=0, `customer_votes`=0, `items_sold'=0

Sorry, my mistake.

Thi line report me an error:

Errore

Pare che ci sia un errore nella query SQL immessa. L'errore del server MySQL mostrato qui sotto, se c'è, può anche aiutare nella risoluzione del problema

ERROR: Virgolette Non Chiuse @ 86
STR: `
SQL: UPDATE `SC_products`  SET `viewed_times`=0, `customers_rating`=0, `customer_votes`=0, `items_sold'=0


query SQL:

UPDATE `SC_products` SET `viewed_times`=0, `customers_rating`=0, `customer_votes`=0, `items_sold'=0

Messaggio di MySQL: Documentazione
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1