Modified hawkwynd's very handy SQL script to delete all customer/order data from the database BEFORE a specific launch date. Also tested in ZC 1.3.9h
Modifications:
- Enhanced script so that you need to specify the date string only once, at the top inside a variable!
- fixed a bug where he used the wrong date selection operator!
Code:
DELETE FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
should be:
Code:
DELETE FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < '2012-10-27');
- Added SQL script to reset the AUTO_INCREMENT value of orders table for this who need that.
As always, backup your DB before trying this!
1) Script to reset the auto_increment value of ORDERS table:
(USE ONLY IF YOU HAVE DELETED ALL ORDER RELATED ENTRIES OR YOU MIGHT END UP WITH DATA INCONSISTENCIES)
This SQL script will reset the AUTO_INCREMENT value of the orders table to 1, meaning that your next order will get the order_number 1 in zen-cart!
Replace YOUR_ZC_DB_NAME_HERE with your actual Zen-Cart db name!
Code:
select table_schema, table_name, table_rows, auto_increment from information_schema.tables where table_schema = 'YOUR_ZC_DB_NAME_HERE' and table_name = 'orders'
ALTER TABLE YOUR_ZC_DB_NAME_HERE.ORDERS AUTO_INCREMENT=1;
select table_schema, table_name, table_rows, auto_increment from information_schema.tables where table_schema = 'YOUR_ZC_DB_NAME_HERE' and table_name = 'orders'
2) Enhanced/corrected DB customer/order cleanup script:
Code:
--
-- Delete customers FROM database, where the account creation date is before the launch date you specify in variable @LAUNCH_DATE
--
-- set LAUNCH_DATE BELOW (set to a future date to remove ALL customer / order entries
-- ******************************************************************
SET @LAUNCH_DATE := '2014-12-31';
-- ******************************************************************
SET @DATE_NO_DELIMITER := REPLACE(@LAUNCH_DATE, '-', '');
SELECT 'all customer/order info before this date will be deleted: ' as info, @LAUNCH_DATE,@DATE_NO_DELIMITER;
DELETE FROM customers WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE);
DELETE FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE);
DELETE FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE);
DELETE FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE);
DELETE FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE);
DELETE FROM customers_info WHERE customers_info_date_account_created < @LAUNCH_DATE;
--
-- Delete all orders dated before @LAUNCH_DATE
--
DELETE orders_total FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;
DELETE orders_status_history FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;
DELETE orders_products_download FROM orders_products_download
INNER JOIN orders ON orders.orders_id = orders_products_download.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;
DELETE orders_products_attributes FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;
DELETE orders_products FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;
DELETE orders FROM orders WHERE date_purchased < @LAUNCH_DATE;
--
-- Reset Statistics
-- Hit Counter Started = 20121027 (launch Date) / Or current date?
-- Hit Counter = 0
--
DELETE counter_history from counter_history where startdate < @DATE_NO_DELIMITER;
UPDATE counter set startdate = @DATE_NO_DELIMITER, counter='0';
Bookmarks