Delete old orders (before specific date)

[rayring]

Dear Zenners,

Ik have a shop with a large amount of orders, I want to remove all old orders (not customers) before 01-01-2016. I am able to go into PHP Myadmin but I am not sure what SQL command I must use, hope someone has an example for me?

Kind regards,

Raymond

[swguy]

Take a backup first!

If the last order you want is 163137 and the table prefix is zen_ do:

DELETE FROM zen_orders WHERE orders_id < 163137;
DELETE FROM zen_orders_products_attributes WHERE orders_id < 163137;
DELETE FROM zen_orders_products WHERE orders_id < 163137;
DELETE FROM zen_orders_products_download WHERE orders_id < 163137;
DELETE FROM zen_orders_status_history WHERE orders_id < 163137;
DELETE FROM zen_orders_total WHERE orders_id < 163137;
DELETE FROM zen_paypal WHERE order_id < 163137;

If you have another payment method that has its own table, do that one too.

[mc12345678]

Don't forget that in order to maintain PCI compliance that an amount of history must be maintained and that keeping a back up of the database would support meeting that requirement. Also, would want a backup just in case something goes wrong.

Suggest reviewing the related threads below, at least specifically this one: https://www.zen-cart.com/showthread....ounter-history

As DrByte said, there are a number of tables and data related to things, but also understand that the individuals posting were wanting to remove orders AND customers.

Also, for "sanity's" sake, before executing a delete query, I would recommend using a select query to validate that the results to be deleted are the ones expected.

So instead of:

DELETE FROM orders where orders_id = 5;

For example, would want to use something like:

SELECT date_purchased FROM orders where orders_id = 5;

Then review the results. If that order or set of orders is the one desired, then change the select (parameter) to just DELETE.

Note: instead of a specific field such as date_purchased, you could use * to get all fields.