.png){kind=logo}
UPSERT Endpoint (POST)
- 31 Mar 2023
- 4 Minutes to read
- Print
- DarkLight
UPSERT Endpoint (POST)
- Updated on 31 Mar 2023
- 4 Minutes to read
- Print
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
UPSERT Endpoint(POST)
Initial Preparation
The Endpoint is accessed through the url {{baseURL }}/CSSAPI/V2/{{entityName}} /Upsert
- baseURL – your company’s ContractInsight URL
- entityName – name of the database table
The HTTP request type is POST. Content type for this request is set to application/json.
.png)
Introduction
UPSERT Endpoint allows you to add/update records in Contract Insight database. UPSERT first attempts to update records. If there is nothing to update, a new record is created or the target entity. The body of UPSERT request is identical to one used for UPDATE request with the exception of having additional attribute – AllowMultiUpdate. AllowMultiUpdate determines whether to perform update when multiple entries are found based on request parameters or only allow single-record update.
Although UPSERT request format is almost the same as one for UPDATE, we need to keep in mind that UPSERT checks to see if records exist for update. If there are no records found, INSERT is executed. Therefore, we must include all attributes for the record in Tuples collection of the request. In other words, we must construct our UPSERT request as if it is an INSERT to begin with. Otherwise, we might end up with a record with missing values for some columns in the target entity in the case of INSERT. Things to keep in mind when making calls to UPDATE Endpoint:
- We can only perform a single table update per request. The total number of updated records cannot exceed 500.
- When AllowMultiUpdate is set to false and REST API finds more than one record for the request, operation will be cancelled, and an error will be returned. AllowMultiUpdate has default value of false in REST API.
- When no records are found for the request, UPSERT endpoint will perform INSERT operation based on values supplied in Tuples collection. Therefore, it is necessary to include all column names and their values in the UPSERT request.
- There are two types of responses. One set of responses is the same as the one for UPDATE endpoint. And the other set is the same as the one for INSERT endpoint.
- Fields that cannot be updated include fields of type GUID (uniqueidentifier), password field, out-of-the-box date fields such as DateEntered, EntryDate, DateCreated, CreatedDate.
Request format
The following format must be used in the request body when making calls to UPSERT Endpoint.
.png)
.png)
Response
Since UPSERT is a combination of UPDATE and INSERT, successful and error responses are the same as for those two endpoints. Please refer to sections on UPDATE and INSERT for more information.
One new error response that a client will encounter is when AllowMultiUpdate attribute is set to false, and the number of records to-be-updated is greater than 1.
.png)
The error means that five records were returned based on the Clause portion of a request. Since AllowMultiUpdate is false, no records were updated.
UPSERT Endpoint in Action (Postman Examples)
Task 1: Regular Update operation
In the example below we are updating records returned based on the Where clause of our request. There happens to be 5 records that satisfied the criteria. We set AllowMultiUpdate flag to “true”. As a result, UPSERT endpoint executed update operation and returned UPDATE response.
Request Body | |
|
|
SQL Equivalent | Result (Partial) |
| |
Task 2: Multiple Updates were set to be disallowed.
In this example, we send the same request as in previous example, except this time, we set AllowMultiUpdate flag to false. An error will be returned.
Request Body | |
|
|
SQL Equivalent | Result |
|
|
Task 3 Insert example
In this example we continue working on Customers table. This time, we keep the same request structure. AllowMultiUpdate will be set to false, but we change our where clause so that REST API gets no records found in the database (we set vendor name to a non-existing vendor name). This will automatically trigger INSERT action. And the response returned will be of INSERT type of response.
* (NOTE: Please remember that UPSERT request must be made as if it is an INSERT. Meaning, Tuples item collection must have every field that you would typically include in INSERT request).
Request Body | |
|
|
SQL Equivalent | Result |
|
|
Partial snapshot of the database record created as a result of the previous request:
.png)
As it could be seen from the picture above, some of the columns have NULL values. That is because only those values supplied in Tuples collection are used in the INSERT operation.
Was this article helpful?
