Salesforce Docs Portal

SQL Where Clause for Salesforce Data Recovery

Use SQL where clauses during recovery, repair, and restore processes to filter and select specific records. SQL where clauses give you precise control over which data is restored or repaired.

SQL Where Clause Examples

Use SQL statements to return a collection of records that match your criteria.

Example
Example
  • Filter all Account records where the 'IsPartner' field is checked:
    • isPartner = 'true'
    • Note that the IsPartner field on the Account object is available only in Orgs where partner relationship management (partner portal) is enabled.
  • Only keep records that have two specific Record Types:
    • RecordTypeId in ('01235000001BJrlAAW', '01215000001BJrLAAW')
  • Only keep records whose name field contains the word 'acme':
    • Name LIKE '%acme%'
  • Only keep records where a custom field (for example, 'Value__c') is empty:
    • Value__c = ''
  • Only keep records that have a value between 2 values (for example, between 100 and 200):
    • cast(Value__c as integer) > 100 AND cast(Value__c as integer) <= 200
  • Keep records that meet one of several conditions (Red, Blue or Green):
    • Color__c IN ('Red', 'Blue', 'Green')
    • Color__c = 'Red' OR Color__c = 'Blue' OR Color__C = 'Green'
  • Keep records that must meet several conditions:
    • Cast(Height as integer) > 15 AND cast(Width as integer) < 5 AND cast(Depth as integer >= 10
  • Keep records that do not match the provided value(s):
    • Country != 'USA'
    • Country NOT IN ('USA', 'Canada')
  • Replicate Accounts based on a checkbox and bring the parent account of those accounts:
    • WHERE CopyToDev__c = 'true' OR Id IN (SELECT ParentId FROM Account WHERE CopyToDev__c = 'true')
  • Only keep records that were created on a specific date (for example, November 27, 2017):
    • CreatedDate = '2017-11-27'
  • Only keep records that were NOT created on a specific date:
    • CreatedDate != '2017-11-27'
  • Find all records added after a specific date:
    • CreatedDate >; '2019-09-16T00:00:00.000Z'
      Note
      Note You need to update the date above to whichever date is applicable.
  • Replicate a subset of record for each RecordType, for example, 5 records of each of my 3 Accounts RecordType
    • Id IN (SELECT Id FROM Account WHERE RecordTypeId='A' LIMIT 5) OR Id IN (SELECT Id FROM Account WHERE RecordTypeId='B' LIMIT 5) OR Id IN (SELECT Id FROM Account WHERE RecordTypeId='C' LIMIT 5)
  • Replicate a subset of records where we have 2 Parents and related Junction Object
    • To illustrate that example, let’s consider we have Account and Survey as a Parent object and an object called Survey_Answer that is our Junction Object.
    • On the Replicate Job, select Account and Survey.
    • Click on the gear next to Account and select a subset of date you would like.
    • Click on the gear next to the Parent Object Survey and run the following query: Id in (SELECT Survey__c FROM Account$Survey_Answer__c).
    • Click on the gear of Survey_Answer (Under the object Survey) and run the query: Id in (Select Id from Account$Survey_Answer__c).

Considerations and Limitations when Using the SQL Where Clause

  • Field values are case-sensitive e.g. Color__c IN ('Red', 'red') "Red", and "red" are considered two different values.
  • Field names are NOT case-sensitive, so (IsPartner = ispartner)
  • Custom field names must be in the API name format; e.g. Field_Name__c
  • When using the 'IN' operator with record IDs, you must use the 18-char version (not the 15 char)
  • When using the 'LIKE' operator with records IDs, you can use the 15-char format, because this looks for a partial match
  • The '%%' operator is NOT case-sensitive
  • Performing a where clause on lookup fields requires the use of the record id as the value e.g. Lookup__c = '01215000001BJrLAAW'

Comparison Operators for the SQL Where Clause

  • = Equals
  • != Not equals
  • < Less than
  • <= Less than or equal to
  • > Greater than
  • >= Greater than or equal to
  • LIKE Like
  • IN In
  • NOT IN Not in