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

Configuring UPSERT-s

UPSERT-s can only be performed on a column which is explicitly defined to serve as the record identifier. You need to provide the name of this column in the ExternalIdColumn column, as shown in the following query:

UPSERT INTO Contacts (`First name`, `Email`, `Upsert Property`, `ExternalIdColumn`) 
VALUES ('John', '[email protected]', '31d06273-de69-4150-9ece-764d267222f6', 'Upsert Property')
VALUES ('Jane', '[email protected]', 'f80ccbab-598e-4e44-8f9b-95d40182e196', 'Upsert Property')

The identifier column must not be read-only and its values must be unique to each record in the table/HubSpot object. You can read the IsReadOnly and HasUniqueValues columns from sys_tablecolumns to find out the eligible UPSERT identifier columns. Refer to the query below:

SELECT * FROM sys_tablecolumns WHERE TableName = 'Contacts' AND IsReadOnly = false AND HasUniqueValues = true 

UPSERT syntax

The UPSERT syntax is the same as the syntax for the INSERT statement:

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  
<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

HubSpot 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 create the record must be specified. See データモデル for any table-specific information.

You can use the executeUpdate method of the Statement class and the executeBatch method of the PreparedStatement class to execute data manipulation commands and retrieve the rows affected.

To retrieve the generated key columns of the last UPSERT-ed record use the getGeneratedKeys method. Additionally, set the RETURN_GENERATED_KEYS flag of the Statement class when using prepared statements, as shown in the following example:

String cmd = "UPSERT INTO Contacts (`First name`, `Email`, `Upsert Property`, `ExternalIdColumn`) VALUES (?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(cmd, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, "John");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setString(3, "31d06273-de69-4150-9ece-764d267222f6");
preparedStatement.setString(4, "Upsert Property");
preparedStatement.addBatch();

preparedStatement.setString(1, "Jane");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setString(3, "f80ccbab-598e-4e44-8f9b-95d40182e196");
preparedStatement.setString(4, "Upsert Property");
preparedStatement.addBatch();

int count = preparedStatement.executeBatch();
System.out.println(count + " rows were affected.");

ResultSet rs = preparedStatement.getGeneratedKeys();
while(rs.next()){	  
  System.out.println(rs.getString("Id"));
}

rs.close();
preparedStatement.close();
connection.close();