Remove ALL customers and ALL orders before a certain date and reset counter history.

[hawkwynd]

v1.50 - Fresh installation

Be sure to backup your database before running any scripts against your zen cart database.

After extensive testing on the zen cart I was preparing for my customer, I noticed we had accrued hundreds of test orders and several fake (test) customer accounts now existed in the database.

Now, when I am ready to launch the site for the customer, I needed to flush the database of orders and reset the counters as well as drop all the test customers from the database, so it was clean of false orders and fake customer accounts.

After careful analysis, I came up with this script which works for my needs, and I share it for anyone who is looking to perform a mysql script to reset the orders and customers as well as zero out the counter for their store.

Be sure to backup your database before running any scripts against your zen cart database.

Also, the table names may be different from yours, as these table names do no use the zc_ prefix, so you may need to edit this script to match your prefix settings on your tables.

I would reccommend executing this script from the mysql server, or in PhpMyAdmin and not in the SQL patch function in the administration tool.

Be sure to backup your database before running any scripts against your zen cart database.

The script will perform the following actions:

• DELETE customers accounts created before 2012-10-27
• DELETE orders placed before 2012-10-27
• DELETE counter_history records before 2012-10-27
• Set the counter start date to 2012-10-27
• Set the hit counter to 0

Oh, and I almost forgot to give you the most important part of this share:

Be sure to backup your database before running any scripts against your zen cart database.


I hope this helps anyone who can use this script.

--
-- Delete customers FROM database, where the account creation date is before the launch date 2012-10-27
--

DELETE FROM customers WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < '2012-10-27');
DELETE FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_info WHERE customers_info_date_account_created > '2012-10-27';

--
-- Delete all orders dated before October 27, 2012
--

DELETE orders_total FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_status_history FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products_download FROM orders_products_download
INNER JOIN orders ON orders.orders_id = orders_products_download.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products_attributes FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders FROM orders WHERE date_purchased <= '2012-10-27';

--
--  Reset Statistics
--  Hit Counter Started = 20121027 (launch Date) / Or current date?
--  Hit Counter = 0
--

DELETE counter_history from counter_history where startdate < '20121027';
UPDATE counter set startdate = '20121027', counter='0';

[fr1dol1n]

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!

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:

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!

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:

--
-- 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';

[fr1dol1n]

After checking the DB schema of ZC 1.3.9 I just realized that there are a few tables missing in hawkwynd's script that also contain an order_id.
So for consistency's sake we need to clear those as well, especially if you do reset the AUTO_INCREMENT value of the orders table!

Those tables are:

• authorizenet
• paypal
• paypal_testing

I am not sure whether it is wise to clear those 2 coupon related tables as well - as I have not yet used coupons. Therefore I commented out the coresponding delete-from-table lines in the script below. Use as you see fit...

• coupon_redeem_track
• coupon_gv_queue

Here's the complete, updated script once more:

--
-- 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 * FROM paypal_testing 
INNER JOIN orders ON orders.orders_id = paypal_testing.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;

DELETE * FROM paypal
INNER JOIN orders ON orders.orders_id = paypal.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;

DELETE * FROM authorizenet
INNER JOIN orders ON orders.orders_id = authorizenet.orders_id
WHERE orders.date_purchased < @LAUNCH_DATE;


-- IF YOU ARE SURE TO CLEAR THESE 2 TABLES, REMOVE THE SQL COMMENTS '--' IN FRONT OF THE NEXT 6 LINES

-- DELETE * FROM coupon_redeem_track
-- INNER JOIN orders ON orders.orders_id = coupon_redeem_track.orders_id
-- WHERE orders.date_purchased < @LAUNCH_DATE;

-- DELETE * FROM coupon_gv_queue
-- INNER JOIN orders ON orders.orders_id = coupon_gv_queue.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';

[DrByte]

There are many more improvements one could make to this whole concept, such as merely truncating the table when you're intending to delete everything in it and reset its autoincrement counter, since that also optimizes the table, removing wasted space. Also, deleting orders for which there is no longer any corresponding customer record after you've been deleting customer records. And instead of deleting from other tables based on order-date, delete where there is no corresponding order record. And when done, use the Store Manager's database-optimize menu to remove wasted space from tables you've been deleting from.
Plus, there are more tables affected. See http://www.zen-cart.com/wiki/index.p...er_Information

But by far the most prudent is to do all your setup and testing in a separate database, and then go live in a clean database.
This has the important added benefit of keeping order numbers together in sequence, something which any bookkeeping auditor wants to see. Those folks don't like to see unexplained gaps and deleted transactions.
This also gives you a truly clean database.

[explorer1979]

Hi All,

My site need del some old customers and all inform base on it, range with some year to year...

So I am reference above and make it change like this working.

My base is two value
1. Base on customers last login time from year to year ragne
2. Base on customers last login time from NULL

If any wrong of it, please correct me, please.

SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `address_book`.`customers_id` DESC;


SELECT * FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `address_book`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE address_book FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)


DELETE address_book FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM back_in_stock_notification_subscriptions
INNER JOIN customers ON `customers`.`customers_id` = `back_in_stock_notification_subscriptions`.`customer_id`
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `back_in_stock_notification_subscriptions`.`customer_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE back_in_stock_notification_subscriptions FROM back_in_stock_notification_subscriptions
INNER JOIN customers ON `customers`.`customers_id` = `back_in_stock_notification_subscriptions`.`customer_id`
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM coupon_redeem_track WHERE customer_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `coupon_redeem_track`.`customer_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE coupon_redeem_track FROM coupon_redeem_track WHERE customer_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM coupon_redeem_track WHERE customer_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `coupon_redeem_track`.`customer_id` DESC;


DELETE coupon_redeem_track FROM coupon_redeem_track WHERE customer_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `customers_basket`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE customers_basket FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `customers_basket`.`customers_id` DESC;


DELETE customers_basket FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `customers_basket_attributes`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE customers_basket_attributes FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `customers_basket_attributes`.`customers_id` DESC;


DELETE customers_basket_attributes FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `products_notifications`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE products_notifications FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `products_notifications`.`customers_id` DESC;


DELETE products_notifications FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);




------ -----
SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `orders_products`.`orders_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE orders_products FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `orders_products`.`orders_id` DESC;


DELETE orders_products FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `orders_products_attributes`.`orders_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE orders_products_attributes FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `orders_products_attributes`.`orders_id` DESC;


DELETE orders_products_attributes FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);







SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `orders_status_history`.`orders_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE orders_status_history FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `orders_status_history`.`orders_id` DESC;


DELETE orders_status_history FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);







SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `orders_total`.`orders_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE orders_total FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `orders_total`.`orders_id` DESC;


DELETE orders_total FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM paypal_payment_status_history
INNER JOIN paypal ON paypal.paypal_ipn_id = paypal_payment_status_history.paypal_ipn_id
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `paypal_payment_status_history`.`paypal_ipn_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE paypal_payment_status_history FROM paypal_payment_status_history
INNER JOIN paypal ON paypal.paypal_ipn_id = paypal_payment_status_history.paypal_ipn_id
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM paypal_payment_status_history
INNER JOIN paypal ON paypal.paypal_ipn_id = paypal_payment_status_history.paypal_ipn_id
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `paypal_payment_status_history`.`paypal_ipn_id` DESC;


DELETE paypal_payment_status_history FROM paypal_payment_status_history
INNER JOIN paypal ON paypal.paypal_ipn_id = paypal_payment_status_history.paypal_ipn_id
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM paypal
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `paypal`.`order_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE paypal FROM paypal
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM paypal
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `paypal`.`order_id` DESC;


DELETE paypal FROM paypal
INNER JOIN orders ON orders.orders_id = paypal.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM worldpay_payments
INNER JOIN orders ON orders.orders_id = worldpay_payments.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `worldpay_payments`.`order_id` DESC;

SET @LAUNCH_DATE := '2012-01-01';
DELETE worldpay_payments FROM worldpay_payments
INNER JOIN orders ON orders.orders_id = worldpay_payments.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM worldpay_payments
INNER JOIN orders ON orders.orders_id = worldpay_payments.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `worldpay_payments`.`order_id` DESC;


DELETE worldpay_payments FROM worldpay_payments
INNER JOIN orders ON orders.orders_id = worldpay_payments.order_id
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM orders
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `orders`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE orders FROM orders
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM orders
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `orders`.`customers_id` DESC;


DELETE orders FROM orders
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);








SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM customers WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE)
ORDER BY `customers`.`customers_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE customers FROM customers
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon < @LAUNCH_DATE);


SELECT * FROM customers WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL)
ORDER BY `customers`.`customers_id` DESC;


DELETE customers FROM customers
WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_of_last_logon IS NULL);







SET @LAUNCH_DATE := '2012-01-01';
SELECT * FROM customers_info
WHERE customers_info_date_of_last_logon < @LAUNCH_DATE
ORDER BY `customers_info`.`customers_info_id` DESC;


SET @LAUNCH_DATE := '2012-01-01';
DELETE customers_info FROM customers_info
WHERE customers_info_date_of_last_logon < @LAUNCH_DATE;


SELECT * FROM customers_info
WHERE customers_info_date_of_last_logon IS NULL
ORDER BY `customers_info`.`customers_info_id` DESC;


DELETE customers_info FROM customers_info
WHERE customers_info_date_of_last_logon IS NULL;