+ Start a Discussion
CarlBCarlB 

Finding if multiple values exist in a single SOQL query

Hello

I have an update trigger that looks to see if a particular field has changed in the trigger records.  If the value has changed, the old value is added to a set.

After all the records have been checked, I end up with a set of up to 200 values (assuming the trigger has handled 200 updated records, for which all records have had the field changed and the original value for all the records were different).

I now want to see if there are any other records that still have each of those original values.

For example, suppose that the field in question is a string and that two records have been updated.  For the first record, the field has changed from 'A' to 'B' and the second record the field has changed from 'C' to 'D'.  At the end of the trigger, I want to see if there are any records that have a field value of 'A' or 'C'.

The obvious solution is to do [ Select X__c from A__c Where X__c In ('A', 'C') ]

However this may return thousands of records.

The alternative is to run [ Select X__c From A__c Where X__c = 'A' Limit 1] then [ Select X__c From A__c Where X__c = 'C' Limit 1], but obviously if there are 200 different values to check, this solution isn't practical.

Is there an alternative approach that I am missing?

Thanks,

Carl
 
Deepak Kumar ShyoranDeepak Kumar Shyoran
Why don't you try to use those 200 recent updated records Id in where part to filter over a set of records like 

 [ Select X__c from A__c Where X__c In ('A', 'C')  and Id in  : trigger.New] ;
CarlBCarlB
Hello.

Unfortunately that does not address my issue, since that will only look at the records that have been updated, not the rest of the records. 

For example, suppose my table contains 200,000 records, of which 50,000 have a value of 'A', 50,000 have a have of 'B' and the rest have a value of 'C'. 

If I update 200 records, schanging 100 records from 'A' to 'D' and 100 records from 'B' to 'E', in my trigger I want to see if there are still some records with values of 'A' or 'B' (of which there will be 49,800 of A and the same number of 'B').

Any solution I think of seems likely to hit Salesforce limits

Thanks,

Carl

 
Deepak Kumar ShyoranDeepak Kumar Shyoran
Ohh. I think Batch class will help you to shot this as you're messing with large set of data, try batch class with trigger and pass data to process from trigger to this batch class for further process to check updated values in other records.
CarlBCarlB
Hello.

So the only way to do it is to run a batch that processes the 99,600 records that are either 'A' or 'B', just to find out if at least one record of 'A' and/or at least one record of 'B' exists.

Wow, that seems really inefficient.

Carl