[Vtigercrm-developers] Purge / clear data

Prasad prasad at vtiger.com
Thu Jun 27 15:18:19 GMT 2019


Thanks for chiming in quick.

My two cents...

DELETE FROM might fail after truncating vtiger_crmentity
TRUNCATE on data-table is better instead.
--
FB <http://www.facebook.com/vtiger> I Twit <http://twitter.com/vtigercrm> I
LIn <https://www.linkedin.com/company/1270573?trk=tyah> I Blog
<https://blogs.vtiger.com> I Website <https://www.vtiger.com/>


On Thu, Jun 27, 2019 at 7:47 PM VTE <support at vtexperts.com> wrote:

>
> 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
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20190627/e677f959/attachment-0001.html>


More information about the vtigercrm-developers mailing list