Bulk / Batch Delete Via Import Update(Create new records and update existing records)

👨‍💻 Development Help
1

Hi

I’m thinking of uploading a batch of IDs with a Delete Column all set to 1 to trigger a Batch Delete.

Import with the “Create new records and update existing records” option

Does anyone know if this method of deleting records will be clean, for example, cause SuiteCRM to delete related data etc?

Many Thanks

Luke

2

You can do a simple test with just one record to see if it does “cascaded-deletes”. My guess is that it won’t, I’ve never seen SuiteCRM do that sort of thing.

I would do it from MySQL, not from the import (much faster, and I believe with better control, a better sense of what’s happening).

Also in MySQL you can come up with queries to look for orphaned relationships and delete them. I have a few of those queries to put in a future blog post, but I haven’t been having any time to update my blog, unfortunately…

Stuff like this to remove custom fields entries after removing a contact:


delete  ChildTable
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c	 = ParentTable.id
WHERE ParentTable.id IS NULL

You can adapt that to other tables. Be VERY careful with SQL, you can ruin your database in seconds, so make sure you start with a backup, and run every query as a SELECT before you change it into a DELETE…

1 Like
3

Thanks pgr, quick to help as ever!

In this scenario I have created a custom module, which has a one to many relationship with contacts.

I did some tests, uploading records to update deleted = 1, and then running the scheduled Prune Database.

SuiteCRM is smart enough to delete records from the modules corresponding cstm table, but it will not mark records for delete in the table that tracks the related IDs.

So you’re right, it’s not and ideal method for bulk deleting.

Thanks

Luke