Apex Code Development You need to sign in to do that
Don't have an account?
CarlB 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
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
[ Select X__c from A__c Where X__c In ('A', 'C') and Id in : trigger.New] ;
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
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