Sign in
Table
Data

Merge insert (upsert) records into a table

Performs a merge insert (upsert) operation on table id. This operation updates existing rows based on a matching column and inserts new rows that don't match. It returns the number of rows inserted and updated.

REST NAMESPACE ONLY REST namespace uses Arrow IPC stream as the request body. It passes in the MergeInsertIntoTableRequest information in the following way:

  • id: pass through path parameter of the same name
  • on: pass through query parameter of the same name
  • when_matched_update_all: pass through query parameter of the same name
  • when_matched_update_all_filt: pass through query parameter of the same name
  • when_not_matched_insert_all: pass through query parameter of the same name
  • when_not_matched_by_source_delete: pass through query parameter of the same name
  • when_not_matched_by_source_delete_filt: pass through query parameter of the same name
post/v1/table/{id}/merge_insert

Path parameters

idstring required

string identifier of an object in a namespace, following the Lance Namespace spec. When the value is equal to the delimiter, it represents the root namespace. For example, v1/namespace/$/list performs a ListNamespace on the root namespace.

Query parameters

delimiterstring

An optional delimiter of the string identifier, following the Lance Namespace spec. When not specified, the $ delimiter must be used.

onstring required

Column name to use for matching rows (required)

when_matched_update_allboolean

Update all columns when rows match

when_matched_update_all_filtstring

The row is updated (similar to UpdateAll) only for rows where the SQL expression evaluates to true

when_not_matched_insert_allboolean

Insert all columns when rows don't match

when_not_matched_by_source_deleteboolean

Delete all rows from target table that don't match a row in the source table

when_not_matched_by_source_delete_filtstring

Delete rows from the target table if there is no match AND the SQL expression evaluates to true

timeoutstring

Timeout for the operation (e.g., "30s", "5m")

use_indexboolean

Whether to use index for matching rows

Response

Result of merge insert operation

transaction_idstring

Optional transaction identifier

num_updated_rowsinteger

Number of rows updated

num_inserted_rowsinteger

Number of rows inserted

num_deleted_rowsinteger

Number of rows deleted (typically 0 for merge insert)

versioninteger

The commit version associated with the operation

Example response

{
  "transaction_id": "transaction_id",
  "num_inserted_rows": 0,
  "num_updated_rows": 0,
  "num_deleted_rows": 0,
  "version": 0
}

Changes