[Vtigercrm-developers] Purge / clear data

[VTE]

Start with:

update vtiger_crmentity set deleted='1';
truncate vtiger_crmentity;

and run the below:

### Delete


DELETE FROM `vtiger_account` WHERE accountid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_accountbillads` WHERE accountaddressid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM `vtiger_accountscf` WHERE accountid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_accountshipads` WHERE accountaddressid NOT IN (SELECT
crmid FROM vtiger_crmentity);

### Contacts

DELETE FROM `vtiger_contactaddress` WHERE contactaddressid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM `vtiger_contactdetails` WHERE contactid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM `vtiger_contactscf` WHERE contactid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_contactsubdetails` WHERE contactsubscriptionid NOT IN
(SELECT crmid FROM vtiger_crmentity);

### Leads

DELETE FROM `vtiger_leadsubdetails` WHERE leadsubscriptionid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM `vtiger_leadaddress` WHERE leadaddressid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM `vtiger_leadscf` WHERE leadid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_leaddetails` WHERE leadid NOT IN (SELECT crmid FROM
vtiger_crmentity);



### Services
DELETE FROM vtiger_service WHERE serviceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_servicecf WHERE serviceid NOT IN (SELECT crmid FROM
vtiger_crmentity);

### products

DELETE FROM vtiger_products WHERE productid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_productcf WHERE productid NOT IN (SELECT crmid FROM
vtiger_crmentity);

## sales orders
DELETE FROM `vtiger_salesorder` WHERE salesorderid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_salesordercf` WHERE salesorderid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM `vtiger_sobillads` WHERE sobilladdressid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM `vtiger_soshipads` WHERE soshipaddressid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM `vtiger_inventoryproductrel` WHERE id NOT IN (SELECT crmid FROM
vtiger_crmentity);

### Activity

DELETE FROM vtiger_activity WHERE activityid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_activitycf` WHERE activityid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_activity_reminder` WHERE activity_id NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM `vtiger_emaildetails` WHERE emailid NOT IN (SELECT crmid FROM
vtiger_crmentity);


#comments
DELETE FROM vtiger_modcomments WHERE modcommentsid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_modcommentscf` WHERE modcommentsid NOT IN (SELECT crmid
FROM vtiger_crmentity);


# assets

DELETE FROM vtiger_assets WHERE assetsid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_assetscf` WHERE assetsid NOT IN (SELECT crmid FROM
vtiger_crmentity);

# invoice
DELETE FROM vtiger_invoice WHERE invoiceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_invoicecf` WHERE invoiceid NOT IN (SELECT crmid FROM
vtiger_crmentity);

# sc

DELETE FROM vtiger_servicecontracts WHERE servicecontractsid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM `vtiger_servicecontractscf` WHERE servicecontractsid NOT IN
(SELECT crmid FROM vtiger_crmentity);

# Oppts
DELETE FROM vtiger_potential WHERE potentialid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_potentialscf WHERE potentialid NOT IN (SELECT crmid FROM
vtiger_crmentity);

# Leads
DELETE FROM vtiger_leaddetails WHERE leadid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_leadsubdetails WHERE leadsubscriptionid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM vtiger_leadaddress WHERE leadaddressid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_leadscf WHERE leadid NOT IN (SELECT crmid FROM
vtiger_crmentity);

# misc

DELETE FROM vtiger_notes WHERE notesid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_troubletickets WHERE ticketid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_ticketcf WHERE ticketid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_products WHERE productid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_faq WHERE id NOT IN (SELECT crmid FROM vtiger_crmentity);
DELETE FROM vtiger_activity WHERE activityid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_vendor WHERE vendorid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_pricebook WHERE pricebookid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_quotes WHERE quoteid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_account WHERE accountid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_purchaseorder WHERE purchaseorderid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM vtiger_salesorder WHERE salesorderid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_account WHERE accountid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_invoice WHERE invoiceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_invoicecf WHERE invoiceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_campaign WHERE campaignid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_pbxmanager WHERE pbxmanagerid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_servicecontracts WHERE servicecontractsid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM vtiger_servicecontractscf WHERE servicecontractsid NOT IN
(SELECT crmid FROM vtiger_crmentity);
DELETE FROM vtiger_service WHERE serviceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_servicecf WHERE serviceid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_assets WHERE assetsid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_assetscf WHERE assetsid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_projectmilestone WHERE projectmilestoneid NOT IN (SELECT
crmid FROM vtiger_crmentity);
DELETE FROM vtiger_projectmilestonecf WHERE projectmilestoneid NOT IN
(SELECT crmid FROM vtiger_crmentity);
DELETE FROM vtiger_projecttask WHERE projecttaskid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_projecttaskcf WHERE projecttaskid NOT IN (SELECT crmid
FROM vtiger_crmentity);
DELETE FROM vtiger_project WHERE projectid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM vtiger_projectcf WHERE projectid NOT IN (SELECT crmid FROM
vtiger_crmentity);

#custom

DELETE FROM `vtiger_listings` WHERE listingsid NOT IN (SELECT crmid FROM
vtiger_crmentity);
DELETE FROM `vtiger_listingscf` WHERE listingsid NOT IN (SELECT crmid FROM
vtiger_crmentity);





-----
VTExperts
--
Sent from: http://vtiger-crm.2324883.n4.nabble.com/vtigercrm-developers-f4.html