---
title: "Pyspark SQL: 5 Delta Table Merge Examples"
description: "This post provides five examples of performing a MERGE operation in PySpark SQL, including upserting new records, updating existing ones, deleting matching records, conducting conditional updates or inserts, and merging partial columns. It emphasizes the necessity of Delta Lake for MERGE functionality and suggests using spark.sql for SQL-like expressions."
url: https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/
date: 2024-11-17
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/11/image-3.png?fit=1024%2C768&ssl=1
categories: ["PySpark"]
tags: ["merge", "Pyspark Delta SQL"]
type: post
lang: en
---

# Pyspark SQL: 5 Delta Table Merge Examples

Here are **five examples of performing a `MERGE` operation in [PySpark SQL](https://srinimf.com/2023/09/15/how-to-add-conditions-in-pyspark-sql-5-examples/)**.

Related[A Comprehensive Guide to PySpark SQL Merge Query](https://srinimf.com/2024/12/16/a-comprehensive-guide-to-pyspark-sql-merge-query/)

![PySpark Merge Examples](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/05/pexels-photo-7937399-edited.jpeg?resize=867%2C488&ssl=1)*Photo by Nicola Barts on Pexels.com*

1. [Before Steps](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#before-steps)
2. [Example 1: Upsert New Records](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#example-1-upsert-new-records)
3. [Example 2: Update Only](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#example-2-update-only)
4. [Example 3: Delete Matching Records](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#example-3-delete-matching-records)
5. [Example 4: Conditional Update and Insert](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#example-4-conditional-update-and-insert)
6. [Example 5: Partial Columns Merge](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#example-5-partial-columns-merge)
7. [Key Notes](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/#key-notes)

## **Before Steps**

Create a Delta table before you start executing these examples. I have created a Table called tgt, which is a Delta table.

```
from pyspark.sql import SparkSession
# Initialize SparkSession
spark = SparkSession.builder 
    .appName("DeltaTableExample") 
    .enableHiveSupport() 
    .getOrCreate()
# Example DataFrame
data = [(1001, "abc", 21), (1002, "bcd", 30), (1, "Alice", 32)]
columns = ["id", "name", "age"]
df = spark.createDataFrame(data, columns)
# Drop the table if it already exists (optional, to avoid conflicts)
spark.sql("DROP TABLE IF EXISTS default.tgt")
# Write the DataFrame as a Delta table
df.write.format("delta").mode("overwrite").saveAsTable("default.tgt")
# Verify the table creation
spark.sql("DESCRIBE DETAIL default.tgt").show(truncate=False)
```

Output

We can see the path to the Delta Table highlighted in red.

```
+------+------------------------------------+-------------------------+-----------+-----------------------------+----------------------+-------------------+----------------+--------+-----------+----------+----------------+----------------+------------------------+----------+
|format|id                                  |name                     |description|location                     |createdAt             |lastModified       |partitionColumns|numFiles|sizeInBytes|properties|minReaderVersion|minWriterVersion|tableFeatures           |statistics|
+------+------------------------------------+-------------------------+-----------+-----------------------------+----------------------+-------------------+----------------+--------+-----------+----------+----------------+----------------+------------------------+----------+
|delta |e96e5c4d-cbdc-4b8a-84af-e6671247703a|spark_catalog.default.tgt|null       |dbfs:/user/hive/warehouse/tgt|2024-11-17 09:00:58.75|2024-11-17 09:01:01|[]              |3       |3232       |{}        |1               |2               |[appendOnly, invariants]|{}        |
+------+------------------------------------+-------------------------+-----------+-----------------------------+----------------------+-------------------+----------------+--------+-----------+----------+----------------+----------------+------------------------+----------+
```

Advertisements

## **Example 1: Upsert New Records**

This example merges a source DataFrame containing updates (`updatesDF`) into a target Delta table (`targetTable`) based on a unique key (`id`). (Update/Insert).

```
from delta.tables import DeltaTable

# Existing Delta table
targetTable = DeltaTable.forPath(spark, "dbfs:/user/hive/warehouse/tgt")

# Source DataFrame with new or updated records
updatesDF = spark.createDataFrame([
    (1, "Alice", 30),
    (2, "Bob", 28),
    (3, "Carol", 35)
], ["id", "name", "age"])

# MERGE operation
targetTable.alias("target").merge(
    updatesDF.alias("source"),
    "target.id = source.id"
).whenMatchedUpdate(set={
    "name": "source.name",
    "age": "source.age"
}).whenNotMatchedInsert(values={
    "id": "source.id",
    "name": "source.name",
    "age": "source.age"
}).execute()
```

Output

```
df = spark.sql("SELECT * FROM default.tgt")
df.show(truncate=False)
```

```
+----+-----+---+
|id  |name |age|
+----+-----+---+
|3   |Carol|35 |
|1   |Alice|30 | ## The age updated. It takes value from the source
|1001|abc  |21 |
|2   |Bob  |28 |
|1002|bcd  |30 |
+----+-----+---+
```

## **Example 2: Update Only**

This example updates records in the target table if they exist in the source table based on a condition.

```
targetTable.alias("target").merge(
    updatesDF.alias("source"),
    "target.id = source.id"
).whenMatchedUpdate(set={
    "age": "source.age"
}).execute()
```

---

Advertisements

## **Example 3: Delete Matching Records**

This example deletes records from the target table when they match a condition with the source table.

```
targetTable.alias("target").merge(
    updatesDF.alias("source"),
    "target.id = source.id"
).whenMatchedDelete().execute()
```

---

## **Example 4: Conditional Update and Insert**

This example demonstrates using conditional logic in updates and inserts during the merge.

```
targetTable.alias("target").merge(
    updatesDF.alias("source"),
    "target.id = source.id"
).whenMatchedUpdate(condition="source.age > 30", set={
    "name": "source.name",
    "age": "source.age"
}).whenNotMatchedInsert(condition="source.age <= 30", values={
    "id": "source.id",
    "name": "source.name",
    "age": "source.age"
}).execute()
```

---

## **Example 5: Partial Columns Merge**

This example performs a merge operation, updating only a subset of columns in the target table.

```
targetTable.alias("target").merge(
    updatesDF.alias("source"),
    "target.id = source.id"
).whenMatchedUpdate(set={
    "name": "source.name"
}).whenNotMatchedInsert(values={
    "id": "source.id",
    "name": "source.name"
}).execute()
```

## **Key Notes**

- **Delta Lake is required** for the MERGE functionality.
- Use spark.sql for SQL-like expressions within conditions and updates.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F11%2F17%2Fhow-to-perform-merge-5-top-pyspark-examples%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F11%2Fimage-3.png%3Ffit%3D1024%252C768%26ssl%3D1&description=Pyspark%20SQL%3A%205%20Delta%20Table%20Merge%20Examples)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Pyspark%20SQL%3A%205%20Delta%20Table%20Merge%20Examples&body=https%3A%2F%2Fsrinimf.com%2F2024%2F11%2F17%2Fhow-to-perform-merge-5-top-pyspark-examples%2F&share=email)
-

### Like this:

Like Loading…