Some suggestion who can give to me by this SQL CLI

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

[mc12345678]

I am only writing to say be careful.

This "script" does not find items the way you intended. The desire was to work with records that had a date after some point (> @LAUNCH_START_DATE ) and before some point (< @LAUNCH_DATE) the above will take action on all data before the launch date, not just a portion of it. There's probably more to be said, but wanted to point out the difference of the initial criteria and the implementation.

[explorer1979]

Hi mc12345678

Thank you.

Then any good suggestion on above case if I want to del some customers who had login on some cycle (With all connect relationship of their orders record too) base on something like that who haven't login within 1 Year from now etc.