> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Upsert Row

> Update existing rows or insert new rows

If a row with the same key exists, it is updated. Otherwise, a new row is inserted. You can also limit updates to specific columns, so only selected values are changed in matching rows.

## Parameters

| Parameter                                          | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| -------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Unique Key                                         | Column(s) used to match existing records in the target dataset. In many cases, the unique key will be equivalent to your table's primary key, if applicable.                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Use Predicate                                      | Lets you add conditions that specify when to apply the merge.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| Use a condition to filter data or incremental runs | Enables applying conditions for filtering the incoming data into the table.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| Merge Columns                                      | Specifies which columns to update during the merge. If empty, the merge includes all columns.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| Exclude Columns                                    | Defines columns that should be excluded from the merge operation.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| (Advanced) On Schema Change                        | Specifies how schema changes should be handled during the merge process.<ul><li><strong>ignore</strong>: Newly added columns will not be written to the model. This is the default option.</li><li><strong>fail</strong>: Triggers an error message when the source and target schemas diverge.</li><li><strong>append\_new\_columns</strong>: Append new columns to the existing table.</li><li><strong>sync\_all\_columns</strong>: Adds any new columns to the existing table, and removes any columns that are now missing. Includes data type changes. This option uses the output of the previous gem.</li></ul> |

## Example: Upsert vehicle types

A vehicle registry uses `vehicle_id` as the merge key. The merge column is set to `type`, so matching vehicles have their type updated. Vehicles that do not exist in the target table are inserted as new rows.

<div class="table-example">
  **Existing target table**

  | VEHICLE\_ID | TYPE  | REGISTERED\_AT |
  | ----------- | ----- | -------------- |
  | 101         | Bus   | 2023-12-01     |
  | 102         | Train | 2023-12-02     |
  | 103         | Bus   | 2023-12-03     |

  **Incoming dataset**

  | VEHICLE\_ID | TYPE |
  | ----------- | ---- |
  | 101         | Tram |
  | 102         | Bus  |
  | 104         | Bus  |

  **Resulting target table**

  | VEHICLE\_ID | TYPE | REGISTERED\_AT |
  | ----------- | ---- | -------------- |
  | 101         | Tram | 2023-12-01     |
  | 102         | Bus  | 2023-12-02     |
  | 103         | Bus  | 2023-12-03     |
  | 104         | Bus  | null           |

  Because the merge column is set to `TYPE`, Prophecy updates only that column for matching rows. Columns that are not included in the merge remain unchanged. Rows that do not match the merge key are inserted as new rows. In this example, vehicles `101` and `102` have updated `TYPE` values, vehicle `103` remains unchanged, and vehicle `104` is inserted with a `null` value for `REGISTERED_AT` because that column was not included in the incoming dataset.
</div>