An UPSERT statement updates an existing record or creates a new record if an existing record is not identified.

Configuring Upserts

Upserts can only be performed on a field explicitly defined in FinancialForce to be an external key. You need to provide the name of this field in a column called ExternalIdColumn, as shown in the following query:

  UPSERT INTO Lead (FirstName, LastName, Company, External_Id_Column__c, ExternalIdColumn) 
  VALUES ('Bob', 'Thorton', 'Universal Pictures', 12345, 'External_Id_Column__c')

In order for the ExternalIdColumn to show up, modify the FinancialForce connection properties to set the PseudoColumns field to the value '*=*' without the quotes.

An UPSERT statement updates an existing record or create a new record if an existing record is not identified.

UPSERT Syntax

The UPSERT syntax is the same as for INSERT. Certinia uses the input provided in the VALUES clause to determine whether the record already exists. If the record does not exist, all columns required to insert the record must be specified. See Data Model for any table-specific information.

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

You can use the executeUpdate method of the Statement and PreparedStatement classes to execute data manipulation commands and retrieve the rows affected. To retrieve the Id of the last inserted record use getGeneratedKeys. Additionally, set the RETURN_GENERATED_KEYS flag of the Statement class when you call prepareStatement, as shown in the following example:

String cmd = "UPSERT INTO Account (Name) VALUES (?)";
PreparedStatement pstmt = connection.prepareStatement(cmd,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, "John");
int count = pstmt.executeUpdate();
System.out.println(count+" rows were affected");
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()){	  
  System.out.println(rs.getString("Id"));
}
connection.close();