Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I have formatted the opportunity title as below.

AA - ss001 - aaaaaaaa

So I want to remove AA - from the title for all the record.

Note: I have 20000 record. So, how to do using SOQL query.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

There are several options to do this task.

  • Use Apex Data Loader to update your records. If you are good in Excel this is a good option.
  • Create simple page with a commandButton and in the action of the button apply your logic

(below is a untested sample code, get the idea)

public void btnAction(){
  List<Opportunity> allOpps = [SELECT Id, Title__c FROM Opportunity]; // put appropriate field for your task
  for(Opportunity opp : allOpps){
    // apply whatever your string manipulations here to get what you want as the title
    opp.Title__c = opp.Title__c.substring(5);  // //put appropriate field for your task
  }
  upsert allOpps;
}


NOTE :
If you are going to the second option you have to limit the query result since DML statements can process maximum 10000 records at once.
Also before updating records in production you have to ensure that you know that this is the format of all your records(double check them or good to have backup may be with data loader again)

share|improve this answer
    
fyi: using substring(4) will leave a space at the front of the name, usually that is not desired. Title is also not an existing standard field on the opportunity object. –  Samuel De Rycke Apr 28 at 8:32
    
agreed @SamuelDeRycke for both. Posted to give the idea since there may be some other logics also to be applied. Also didn't mention about the fieldname since it's obvious. Anyway edited the answer to keep the forum clean and clear :). Thanks for pointing out. –  highfive Apr 28 at 8:40
    
@highfive I have write validation rule for some of the record so we cant update some of the field after the getting the error. so to skip when through the exception and continue the upsert? –  SFDC LEARNER Apr 28 at 8:47
    
If your current data is not in line with your validation rules, you may need to clean your data or update your rules to not have them be applicable to old data. You can opt to temporarely inactivate your validation rule while executing the anonymous apex. –  Samuel De Rycke Apr 28 at 8:52

SOQL is only to query data, you can not use it to update or change data. To do this you will need to either export your data and use an external tool (excel may work) or you can run apex on your data.

For data operations that need to happen repeatedly you can use scheduled apex, if it only has to be done once you can use anonymous apex. DO BACKUP YOUR DATA BEFORE YOU RUN ANONYMOUS APEX ON PRODUCTION.

If yo have a look at the Apex String methods you'll find that using substring this is easily done, if your opportunity names are always formated in the same way.

string t = 'AA - ss001 - aaaaaaaa';
t = t.substring(5);
system.debug(t); //39997459)|USER_DEBUG|[4]|DEBUG|ss001 - aaaaaaaa

Through the developer console, the eclipse ide or other tools using the salesforce API you can execute the following anonymous apex:

List<opportunity> list = [SELECT Id,name 
                          FROM opportunity 
                          WHERE   put something meaningful here
                          LIMIT 10000];
for(opportunity opt:lst){
      opt.name = opt.name.substring(5);
}
update list;

The update DML statement can only process 10.000 records, so you should limit your query to this amount and repeatedly execute. Using the Where clause you should select those records that have not been modified yet (by filtering on the name, or lastmodified date, .. what makes sense to your scenario)

share|improve this answer
    
I have write validation rule for some of the record so we cant update some of the field after the getting the error. so to skip when through the exception and continue the update? – –  SFDC LEARNER Apr 28 at 8:52
    
If your current data is not in line with your validation rules, you may need to clean your data or update your rules to not have them be applicable to old data. You can opt to temporarely inactivate your validation rule while executing the anonymous apex. I would not recommend skipping those records, as that will just leave your data in an even more inconsistent state. –  Samuel De Rycke Apr 28 at 8:53
1  
Hola Ricky, (creo que hablas español?,perdón si no es asī) si tu validación es que no puedes actualizar el campo nombre entonces este codigo nunca sera ejecutado satisfactoriamente.Sin embargo, si dicha validacion esta comprobando otros campos que actualizáis a la la vez con el nombre y a veces son modificados y otras veces no,lo que puedes usar para que aquellos registros que se puedan actualizar se actualicen y los que no te deje la validacion se queden tal cual, puedes usar: Database.update(sObject [], Boolean) en lugar de upadte list. Donde Boolear ira a false –  Carolina Apr 28 at 9:27
    
Yes, that is probably a better suggestion than setting the validation rule inactive. But I'd still recommend you try to clean your data and/or update the validation rule to not cause these issues. –  Samuel De Rycke Apr 28 at 9:34
1  
Totally true Samuel. I couldn't add the second part of my comment. Where I said, if it was me I would do first what Samuel suggested. :) –  Carolina Apr 28 at 12:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.