Updating Records with SQLForce

SQLForce has two techniques for updating records in Salesforce:

• A standard ANSI UPDATE statement. This is generally better when many records should receive the same update.
• An update() method used when the updated values for each record vary.

With SQLForce you do not need to worry about the Salesforce “200 records per update call” restriction. SQLForce will batch records for you automatically.

SQLForce supports UPDATE statements in the form:

• UPDATE tableName SET col=value [,col=value]* [WHERE condition]

For example:

• UPDATE Account SET ShippingCountry=’United States’ WHERE shippingCountry IN (‘US’, ‘USA’, ‘U.S.A.’, ‘U.S.’, ‘America’)
• UPDATE Contact SET BillingCountry=null WHERE shippingCountry=null

The SQLForce Session.runCommands() method is used to execute these statements. For example:

session = SQLForce.Session(‘MyProfile’)

session.runCommands(“UPDATE Contact SET BillingCountry=null WHERE shippingCountry=null”)

nUpdated = session.getenv(‘ROW_COUNT’)

The session.getenv() call retrieves a SQLForce variable that holds the count of rows affected by the most recent INSERT/UPDATE/DELETE statement.

The Session.update() method is used when the updated value(s) for records vary by record id. The general form of this call is:

• session.update(‘TableName’,  <list of columns to update>, <list of values for each record to update>)

For example:

• session.update(‘Account’, [‘Type’, ‘Industry’], [[‘001A0000013RQ4m’, ‘Biotech’, ‘Commercial’], [‘001A0000013QHiV’, ‘Fuels’, ‘Academic’]] )

In this example:

• The first argument, ‘Account’, is the name of the table to update.
• The second argument contains a list of column names to update. The column name order must match the column data order in the next parameter.
• The third argument contains a list of lists where each sub-list contains new values for an individual record. The first element MUST BE the Id of the record being updated. The following elements are the values to update IN THE SAME ORDER as the columns in the second argument.

The session.update() method either:

• Returns a count of rows updated.
• Throws an exception if the update fails.