> ## 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.

# Delete and Insert

> Delete matching rows and insert new rows

For rows in the target table that have keys that match rows in the incoming dataset, deletes matching rows from the existing table and replaces them. For incoming rows with new keys, inserts these normally. This ensures that updated records are fully replaced instead of partially updated.

<Note>
  This strategy helps when your unique key is not truly unique (multiple rows per key need to be
  fully refreshed).
</Note>

## Parameters

| Parameter                                          | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| -------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Unique Key                                         | Column(s) used to match existing records in the target dataset.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| (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: Refresh order line items

An order table uses `order_id` as the unique key. When a corrected order arrives, Prophecy deletes all existing rows with the matching `order_id` and inserts the incoming rows. Orders with new IDs are inserted normally.

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

  | ORDER\_ID | LINE\_ITEM | PRODUCT  |
  | --------- | ---------- | -------- |
  | 101       | 1          | Laptop   |
  | 101       | 2          | Mouse    |
  | 102       | 1          | Keyboard |

  **Incoming dataset**

  | ORDER\_ID | LINE\_ITEM | PRODUCT |
  | --------- | ---------- | ------- |
  | 101       | 1          | Laptop  |
  | 101       | 2          | Monitor |
  | 101       | 3          | Mouse   |
  | 103       | 1          | Dock    |

  **Resulting target table**

  | ORDER\_ID | LINE\_ITEM | PRODUCT  |
  | --------- | ---------- | -------- |
  | 101       | 1          | Laptop   |
  | 101       | 2          | Monitor  |
  | 101       | 3          | Mouse    |
  | 102       | 1          | Keyboard |
  | 103       | 1          | Dock     |

  Because `order_id` is the unique key, Prophecy deletes all existing rows with matching keys before inserting the incoming rows. In this example, both existing rows for order `101` are removed and replaced with the three incoming rows. Order `102` remains unchanged because it has no matching incoming rows, and order `103` is inserted as a new order.
</div>