Need to delete 18,000 records from Accounts

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hi.

I have 18,000 records to delete from the accounts module.

I’ve got a csv list of all 18,000 id_c records to delete. Only thing is i’m now not really sure how to go about deleting them… Any ideas on the neatest, no actually the easiest way to delete them i’m not used to playing with sql.

I’m quite happy to leave them all in just have them changed from 0 to 1 in the deleted column?

Can I just create a 2nd field called Deleted or whatever it’s called in the database and import all 18,000 records and then hey presto the system will treat them as deleted and not show them up? All ideas appreciated.

Thanks

2

Supposing you have a really good backup, you could try using SQL with a little help from our friends:

  1. Create a new table called ToDelete (all you need is one column, containing the 18000 account ids to delete) and import your data into it

  2. Follow this http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table
    to delete records from the Accounts table, which are present in the ToDelete table.

REMEMBER to invert the logic from what you find in that StackOverflow topic: change NOT EXISTS to EXISTS, IS NULL to IS NOT NULL, etc.

You can try the DELETEs first as SELECTs, to make sure your WHEREs are catching the correct rows.

You do have a really good backup, don’t you? One small mistake and you could have a tragedy in your hands…

1 Like
3

Thanks.

I’ll have a careful look at it before i even attempt it… :slight_smile: yes Veeam nightly backups :slight_smile: and a test server to try it on first.