Need help in identifying the records that needs to be deleted in a custom object: Opportunity_LGT
Background: Opportunity_LGT is a custom object that was created to store few fields of Opportunity standard object for different purposes. When standard Opportunity is created/updated , trigger will create/update Opportunity_LGT. Opportunity_LGT has lookup to Account object and it holds ID of the standard Opportunity record as text field – Opportunity_id_txt.
We recently implemented the delete logic, when opportunity is deleted , corresponding Opportunity_LGT must get deleted.
What is required: I need to delete Opportunity_LGT records which doesn’t have corresponding standard Opportunity record. Oppportunity standard object has 55,000 records and Opportunity_LGT has 55,283 records. I need to delete additional 283 records in Opportunity_LGT records.
Issue:
I have written below code to get those 283 Opportunity_LGT records in an CSV file and send email to me.. I can delete those records via DATALOADER.
When I execute the code on "Open execute anonymous window".. It give error messages – "System.LimitException: Too many query rows: 50001". Because I have more than 50,000 records.. I have spend lot of time on looking at other options.. Looking for a direction here and different approaches.
public class TempDeleteOpplgtwithnoOpp {
Public void getalltodeleterecords()
{
//*Map<ID, Opportunity> Oppmap = new map<id, Opportunity>([SELECT ID, NAME FROM Opportunity LIMIT 10]);
Set<id> oppidlist = new Set<id>();
for(Opportunity opp : [SELECT ID FROM Opportunity])
{
oppidlist.add(opp.id);
}
string OpplgtDelNoOppStringFinal =
'ID'+','+'Name'+','+'CreatedDate'+','+'LastModifiedDate'+','+'Opportunity_Lgt_Name'+','+' Opportunity_id_txt __c'+'\n';
List<id> opplgttoDelIdSet = new List<ID>();
for(Opportunity_LGT__C opplgt : [SELECT ID, Name, CreatedDate, LastModifiedDate, Account_Code__c,
Opportunity_Lgt_Name__c, Opportunity_id_txt __c FROM Opportunity_LGT__C] )
{
IF (!Oppidlist.contains(opplgt.id))
{
opplgttoDelIdSet.add(opplgt.id);
String OpplgtDelNoOppString = opplgt.id +','+opplgt.name+','+opplgt.CreatedDate+','+opplgt.LastModifiedDate+','+opplgt.Opportunity_Lgt_Name__c+','+opplgt.Opportunity_Record_Id__c +'\n';
OpplgtDelNoOppStringFinal = OpplgtDelNoOppStringFinal+ OpplgtDelNoOppString ;
}
}
system.debug('Opplgtlist.size: ' + Oppidlist.size());
system.debug('opplgttoDelIdSet.size:' + opplgttoDelIdSet.size());
Messaging.EmailFileAttachment DelOLNoOpp = new Messaging.EmailFileAttachment();
Blob DelOLNoOppcsvBlob = Blob.valueof(OpplgtDelNoOppStringFinal);
String DelOLNoOppcsvname = 'DelOLNoOppOL.csv';
DelOLNoOpp.setFileName(DelOLNoOppcsvname);
DelOLNoOpp.setBody(DelOLNoOppcsvBlob);
Messaging.singleEmailMessage ademail = new Messaging.singleEmailMessage();
List<String> toAddress = new List<String>{'raghavendra.shettigar@inventivHealth.com'};
ademail.setSubject('OppLightDeleteFiles');
ademail.setToAddresses(toAddress);
ademail.setPlainTextBody('Opportunity Light Delete');
ademail.setFileAttachments( new messaging.emailFileAttachment[]{DelOLNoOpp });
Messaging.SendEmailResult[] r = Messaging.sendEmail(new Messaging.singleEmailMessage[]{ademail});