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 Data Model for any table-specific information.