These post will helpful to maintain the last 30 order details from the website. In case when we move re-store the live database into testing server, we will get all the records from the live system. Those are not required for our test server. So we can maintain only recently datas (Ex: recent 30 order from live) and rest will delete. These 30 orders – customer detail , address detail are still in alive when we run the below scripts.
DELETE CUSTOMERS DATA:
1. customer_entity
create table customer_entity_temp SELECT * FROM customer_entity ORDER BY entity_id DESC LIMIT 30;
truncate table customer_entity;
INSERT into customer_entity select * from customer_entity_temp;
DROP table customer_entity_temp;
2. customer_entity_datetime
DELETE FROM customer_entity_datetime WHERE entity_id NOT IN ( SELECT entity_id
FROM customer_entity );
3. customer_entity_decimal
DELETE FROM customer_entity_decimal WHERE entity_id NOT IN ( SELECT entity_id
FROM customer_entity );
4. customer_entity_int
create table customer_entity_int_temp
select ein.* from customer_entity e inner join customer_entity_int ein on ein.entity_id = e.entity_id ;
truncate table customer_entity_int;
insert into customer_entity_int select * from customer_entity_int_temp;
drop table customer_entity_int_temp;
5. customer_entity_text
DELETE FROM customer_entity_decimal WHERE entity_id NOT IN (
SELECT entity_id FROM customer_entity );
6. customer_entity_varchar
create table customer_entity_varchar_temp s elect ein.* from customer_entity e
inner join customer_entity_varchar ein on ein.entity_id = e.entity_id;
truncate table customer_entity_varchar;
insert into customer_entity_varchar select * from customer_entity_varchar_temp;
drop table customer_entity_varchar_temp;
7. customer_address_entity_datetime – 0 records
delete from customer_address_entity_datetime where entity_id not in
( select ein.entity_id from customer_entity e inner join customer_address_entity ein on ein.parent_id = e.entity_id );
8. customer_address_entity_decimal – 0 records
delete from customer_address_entity_decimal where entity_id not in
( select ein.entity_id from customer_entity e inner join customer_address_entity ein on ein.parent_id = e.entity_id );
9. customer_address_entity_int –
delete from customer_address_entity_int where entity_id not in
( select ein.entity_id from customer_entity e inner join customer_address_entity ein on ein.parent_id = e.entity_id );
10. customer_address_entity_text
delete from customer_address_entity_text where entity_id not in
( select ein.entity_id from customer_entity e inner join customer_address_entity ein on ein.parent_id = e.entity_id )
11.customer_address_entity_varchar
create table customer_address_entity_varchar_temp
select add_varchar.* from customer_entity e
inner join customer_address_entity ein on ein.parent_id = e.entity_id
inner join customer_address_entity_varchar add_varchar on add_varchar.entity_id = ein.entity_id ;
truncate table customer_address_entity_varchar;
insert into customer_address_entity_varchar select * from customer_address_entity_varchar_temp;
drop table customer_address_entity_varchar_temp;
12. customer_address_entity
create table customer_address_entity_temp
select ein.* from customer_entity e inner join customer_address_entity ein on ein.parent_id = e.entity_id set foreign_key_checks=0;
truncate table customer_address_entity;
insert into customer_address_entity select * from customer_address_entity_temp;
drop table customer_address_entity_temp;
set foreign_key_checks=1;
SALES RELATED TABLES
1. sales_flat_order
create table sales_flat_order_temp
select e.* from sales_flat_order e inner join customer_entity cust on cust.entity_id = e.customer_id; set foreign_key_checks=0;
truncate table sales_flat_order;
insert into sales_flat_order select * from sales_flat_order_temp;
drop table sales_flat_order_temp;
set foreign_key_checks=1;
2. sales_flat_order_address
create table sales_flat_order_address_temp
select order_addr.* from sales_flat_order e inner join sales_flat_order_address order_addr on order_addr.parent_id = e.entity_id;
truncate table sales_flat_order_address;
insert into sales_flat_order_address select * from sales_flat_order_address_temp;
drop table sales_flat_order_address_temp;
3. sales_flat_order_grid
DELETE FROM sales_flat_order_grid
WHERE entity_id NOT IN ( SELECT entity_id FROM sales_flat_order );
4. sales_flat_order_item
create table sales_flat_order_item_temp
select order_item.* from sales_flat_order e
inner join sales_flat_order_item order_item on order_item.order_id = e.entity_id;
set foreign_key_checks=0;
truncate table sales_flat_order_item;
insert into sales_flat_order_item select * from sales_flat_order_item_temp;
drop table sales_flat_order_item_temp;
set foreign_key_checks=1;
5. sales_flat_order_payment
DELETE FROM sales_flat_order_payment
WHERE parent_id NOT IN ( SELECT entity_id FROM sales_flat_order );
6. sales_flat_order_status_history
create table sales_flat_order_status_history_temp
select order_history.* from sales_flat_order e
inner join sales_flat_order_status_history order_history on order_history.parent_id = e.entity_id;
truncate table sales_flat_order_status_history;
insert into sales_flat_order_status_history select * from sales_flat_order_status_history_temp;
drop table sales_flat_order_status_history_temp;
7. sales_flat_invoice
DELETE FROM sales_flat_invoice
WHERE order_id NOT IN ( SELECT entity_id FROM sales_flat_order );
Simillarly repeat the above query to tables also. But please check order ID name is “order_id ” or “entity_id”
8. sales_flat_invoice_comment
9. sales_flat_invoice_grid
10. sales_flat_invoice_item
11. sales_flat_creditmemo – truncated because no cmemo for existing orders
set foreign_key_checks=0;
truncate table sales_flat_creditmemo;
set foreign_key_checks=1;
12. sales_flat_creditmemo_comment
truncate table sales_flat_creditmemo_comment;
13. sales_flat_creditmemo_grid
truncate table sales_flat_creditmemo_grid;
14. sales_flat_creditmemo_item
truncate table sales_flat_creditmemo_item;
15. truncate table sales_flat_quote;
truncate table sales_flat_quote_address;
truncate table sales_flat_quote_address_item;
truncate table sales_flat_quote_item;
truncate table sales_flat_quote_item_option;
truncate table sales_flat_quote_payment;
truncate table sales_flat_quote_shipping_rate;
THANKS TO PRADEEP MANI