> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/delete-data-from-a-target-table.md).

# Delete Data from a Target Table

To delete data from your target table, create a transformation job that includes a [`MERGE`](broken://spaces/WKMq8oT1OPM3KjP8vlg2/pages/FoViNkMvl4MA1WrT7Bsn) statement. The job requires that your target table has at least one primary key to match rows between source and target.

If your target table is a data lake table, you can define a primary key as follows:

```sql
CREATE TABLE my_catalog.my_schema.target_table (pk string)
PRIMARY KEY pk;
```

You can then delete data from your target table by running a job with the following format:

```sql
CREATE JOB my_merge_job
AS MERGE INTO my_catalog.my_schema.target_table AS target
USING (
    SELECT f1 AS field1, f2 AS field2, f3 AS field3, pk, to_delete
    FROM my_catalog.my_schema.source_table
    WHERE $time BETWEEN execution_start_time() AND execution_end_time()
) source
ON target.pk = source.pk
WHEN MATCHED AND to_delete THEN DELETE
WHEN MATCHED THEN REPLACE
WHEN NOT MATCHED THEN INSERT MAP_COLUMNS_BY_NAME EXCEPT to_delete;
```

This job first matches your data based on the columns provided in the `ON` condition. If the values match and the delete condition **to\_delete** is met, Upsolver deletes the data from your target table.

If you omit the `ON` statement, there should be a natural join for all primary key and partition columns. In this example, the name of the primary key column **pk** in the target table matches the name of a column in the source table, so this query works even if the `ON` statement is omitted.

{% hint style="info" %}
Note that we've included the [`EXCEPT`](broken://spaces/WKMq8oT1OPM3KjP8vlg2/pages/FoViNkMvl4MA1WrT7Bsn#except) clause here, indicating that the specified column **to\_delete** should be excluded and not inserted into the target table.

For the rest of the columns, the [`MAP_COLUMNS_BY_NAME`](broken://spaces/WKMq8oT1OPM3KjP8vlg2/pages/qeCxDzTz8tD4epsOJhfj) keyword merges data from the source table to the target table based on matching column names.
{% endhint %}

If the column names are different, you can rename the source table column in the `SELECT` statement:

```sql
CREATE JOB my_merge_job
AS MERGE INTO my_catalog.my_schema.target_table AS target
USING (
    SELECT f1 AS field1, f2 AS field2, f3 AS field3, primary_key AS pk, to_delete
    FROM my_catalog.my_schema.source_table
    WHERE $time BETWEEN execution_start_time() AND execution_end_time()
) source
ON target.pk = source.pk
WHEN MATCHED AND to_delete THEN DELETE
WHEN MATCHED THEN REPLACE
WHEN NOT MATCHED THEN INSERT MAP_COLUMNS_BY_NAME
```

Another option is to merge using `(column, ...) values (expression, ...)` instead:

```sql
CREATE JOB my_merge_job
AS MERGE INTO my_catalog.my_schema.target_table AS target
USING (
    SELECT f1 AS field1, f2 AS field2, f3 AS field3, primary_key, to_delete
    FROM my_catalog.my_schema.source_table
    WHERE $time BETWEEN execution_start_time() AND execution_end_time()
) source
ON target.pk = source.primary_key
WHEN MATCHED AND to_delete THEN DELETE
WHEN MATCHED THEN REPLACE
WHEN NOT MATCHED THEN 
    INSERT (col1, col2, col3, pk) 
    VALUES (field1, field2, field3, primary_key)
```

By explicitly mapping the values in the **primary\_key** column of the source table to the **pk** column of the target table, you can match your data using these two columns even though they do not share the same name.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/delete-data-from-a-target-table.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.