---
title: "Why DELETE with Subqueries Fails in PySpark SQL (And How to Fix It)"
description: "Learn why PySpark SQL DELETE with WHERE IN subquery fails and how to fix it using DELETE USING, Delta tables, and join-based deletes."
url: https://srinimf.com/2026/01/01/why-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it/
date: 2026-01-01
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2026/01/pexels-photo-17727918.jpeg?fit=867%2C1300&ssl=1
categories: ["PySpark"]
tags: ["pyspark delete with subquery", "pyspark sql delete"]
type: post
lang: en
---

# Why DELETE with Subqueries Fails in PySpark SQL (And How to Fix It)

If you are coming from a traditional RDBMS background (Oracle, MySQL, PostgreSQL), you might expect SQL statements to behave the same way in **[PySpark SQL](https://srinimf.com/2024/11/17/how-to-perform-merge-5-top-pyspark-examples/)**. However, many data engineers are surprised when a perfectly valid SQL query like:

```
DELETE FROM target_table
WHERE id IN (
    SELECT id
    FROM source_table
    WHERE status = 'INACTIVE'
);
```

Related[Step-by-Step Guide to Reading Parquet Files in Spark](https://srinimf.com/2024/09/11/optimizing-parquet-file-distribution-in-spark-detailed-overview/)

either **fails**, **throws an error**, or **deletes zero rows** in Spark.

This blog explains **why `DELETE` with `WHERE IN (subquery)` often does not work in PySpark SQL**, what Spark actually supports, and the **correct, production-ready alternatives** you should use.

---

## Understanding DELETE in Spark SQL

Spark SQL is **not a traditional database engine**. It was designed for **distributed processing**, not row-level mutations. As a result:

- `DELETE` is **not universally supported**
- Subqueries inside `DELETE` have **strict limitations**
- Behavior depends on **table format** (Delta vs Parquet)

### DELETE is supported only for:

- **Delta tables**
- Some Hive ACID tables

### DELETE is NOT supported for:

- Temporary views
- DataFrames
- Parquet / CSV / JSON tables

---

## Common Mistake: Multiple WHERE Clauses

Many users try something like this:

```
DELETE FROM orders
WHERE country = 'IN'
WHERE order_status = 'CANCELLED';
```

❌ This is invalid SQL.
SQL allows **only one WHERE clause**.

### Correct syntax

```
DELETE FROM orders
WHERE country = 'IN'
  AND order_status = 'CANCELLED';
```

---

## WHERE IN (Subquery) – Valid SQL but Limited in Spark

This query is **logically correct SQL**:

```
DELETE FROM target_table
WHERE id IN (
    SELECT id
    FROM source_table
    WHERE status = 'INACTIVE'
);
```

- One `WHERE` belongs to the `DELETE`
- Another `WHERE` belongs to the subquery

✔️ This is valid SQL
❌ But **Spark SQL may not support it**

---

## Why DELETE with Subqueries Fails in PySpark

### 1. Spark SQL limitations

Spark does not fully support **subqueries inside DML operations** (`DELETE`, `UPDATE`).

### 2. Query optimizer restrictions

Spark prefers **join-based execution**, not nested subqueries, for distributed deletes.

### 3. Non-Delta tables

If `target_table` is not a **Delta table**, the query will fail silently or throw:

- `DELETE is not supported`
- `Subqueries are not supported in DELETE`

---

## Check If Your Table Supports DELETE

Before running DELETE, always verify table format:

```
DESCRIBE DETAIL target_table;
```

Look for:

```
format: delta
```

If the format is **not delta**, `DELETE` will not work.

---

## Best Practice: Use DELETE USING (JOIN-Based Delete)

Spark SQL officially recommends **JOIN-based deletes** instead of subqueries.

Related[2 Top SQL Queries to Update Table: Subquery and Join](https://srinimf.com/2023/11/09/how-to-update-a-table-neat-way-using-subquery/)

### Recommended Solution (Delta Tables)

```
DELETE FROM target_table t
USING source_table s
WHERE t.id = s.id
  AND s.status = 'INACTIVE';
```

### Why this works better

- No subquery limitations
- Optimized for distributed execution
- Fully supported by Delta Lake
- Easier to debug and maintain

---

## Comparison: IN [Subquery vs](https://srinimf.com/2012/07/16/sql-sub-query-and-correlated-query/) DELETE USING

| Approach | Works in Spark | Performance | Recommendation |
| --- | --- | --- | --- |
| IN (subquery) | ❌ Limited | Poor | ❌ Avoid |
| DELETE USING | ✅ Yes | High | ✅ Best |
| Filter + overwrite | ✅ Yes | Medium | ✅ For non-Delta |

---

## Alternative for Non-Delta Tables (Filter & Overwrite)

If your table is **Parquet or Hive**, use the DataFrame approach.

### PySpark DataFrame Solution

```
target_df = spark.table("target_table")
source_df = (
    spark.table("source_table")
    .filter("status = 'INACTIVE'")
    .select("id")
)

final_df = target_df.join(
    source_df,
    on="id",
    how="left_anti"
)

final_df.write \
    .mode("overwrite") \
    .saveAsTable("target_table")
```

### Explanation

- `left_anti` keeps rows **not present** in source
- Mimics DELETE behavior safely
- Works in **AWS Glue, EMR, Databricks**

---

## Real-World Example

### Scenario

Delete cancelled orders created by inactive users.

### ❌ Problematic Query

```
DELETE FROM orders
WHERE user_id IN (
    SELECT user_id
    FROM users
    WHERE active_flag = 'N'
);
```

### ✅ Correct Spark SQL Approach

```
DELETE FROM orders o
USING users u
WHERE o.user_id = u.user_id
  AND u.active_flag = 'N';
```

---

## Common Errors & Fixes

| Error | Cause | Fix |
| --- | --- | --- |
| DELETE not supported | Non-Delta table | Convert to Delta |
| Deletes 0 rows | Subquery limitation | Use DELETE USING |
| Syntax error | Multiple WHERE | Use AND / OR |
| Works in DB, not Spark | Engine difference | Join-based delete |

---

## Best Practices for Data Engineers

- Always use **Delta Lake** if you need DELETE/UPDATE
- Avoid `IN (subquery)` in Spark SQL
- Prefer **DELETE USING**
- For Glue/EMR → use **DataFrame overwrite**
- Validate deletes in **lower environments first**

---

## Conclusion

Although `WHERE IN (subquery)` is valid SQL, **Spark SQL has clear limitations** when it comes [to DELETE](https://srinimf.com/2024/09/03/how-to-delete-source-object-after-glue-job-run-complete/) operations. Relying on traditional SQL patterns can lead to silent failures or unexpected results.

### 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%2F2026%2F01%2F01%2Fwhy-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2026%2F01%2Fpexels-photo-17727918.jpeg%3Ffit%3D800%252C1200%26ssl%3D1&description=Why%20DELETE%20with%20Subqueries%20Fails%20in%20PySpark%20SQL%20%28And%20How%20to%20Fix%20It%29)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2026/01/01/why-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2026/01/01/why-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2026/01/01/why-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2026/01/01/why-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Why%20DELETE%20with%20Subqueries%20Fails%20in%20PySpark%20SQL%20%28And%20How%20to%20Fix%20It%29&body=https%3A%2F%2Fsrinimf.com%2F2026%2F01%2F01%2Fwhy-delete-with-subqueries-fails-in-pyspark-sql-and-how-to-fix-it%2F&share=email)
-

### Like this:

Like Loading…