This is an automated email from the ASF dual-hosted git repository.
szehon-ho pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new a88673dfb2b5 [SPARK-57359][DOC] Document the MERGE INTO statement in
the SQL reference
a88673dfb2b5 is described below
commit a88673dfb2b56407bb1582534413771d12192ec6
Author: Szehon Ho <[email protected]>
AuthorDate: Wed Jun 10 17:19:47 2026 -0700
[SPARK-57359][DOC] Document the MERGE INTO statement in the SQL reference
### What changes were proposed in this pull request?
The SQL reference does not have a page for the `MERGE INTO` statement. This
PR adds one at `docs/sql-ref-syntax-dml-merge-into.md` and links it from the
DML statements list in `docs/sql-ref-syntax.md`.
The new page covers:
- Description, including that `MERGE INTO` requires a Data Source V2
connector that supports row-level operations (linked to the DSV2 reference).
- Syntax for the `ON` condition and the `WHEN MATCHED`, `WHEN NOT MATCHED [
BY TARGET ]`, and `WHEN NOT MATCHED BY SOURCE` clauses, with their allowed
actions (`DELETE`, `UPDATE SET *`, `UPDATE SET col = val`, `INSERT *`, `INSERT
(cols) VALUES (...)`).
- Parameters describing each clause and action.
- Notes on multiple clauses being evaluated in order, the requirement of at
least one `WHEN` clause, and the `MERGE_CARDINALITY_VIOLATION` behavior
(including when the cardinality check is skipped).
- Examples for update/insert, conditional delete/update/insert,
delete-not-in-source, and update-not-in-source.
The `WITH SCHEMA EVOLUTION` clause is intentionally left for a follow-up PR.
### Why are the changes needed?
`MERGE INTO` is a supported SQL statement but has no entry in the SQL
reference, so users have no documentation for its syntax and semantics.
### Does this PR introduce _any_ user-facing change?
No. Documentation-only update.
### How was this patch tested?
Docs-only change. Reviewed the rendered Markdown for correctness.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor (Claude Opus 4.8)
Closes #56420 from szehon-ho/merge-schema-evolution-docs.
Authored-by: Szehon Ho <[email protected]>
Signed-off-by: Szehon Ho <[email protected]>
(cherry picked from commit 8e22e990994a3e4bf35ddaebe0e06fa5b36e284f)
Signed-off-by: Szehon Ho <[email protected]>
---
docs/sql-ref-syntax-dml-merge-into.md | 223 ++++++++++++++++++++++++++++++++++
docs/sql-ref-syntax.md | 1 +
2 files changed, 224 insertions(+)
diff --git a/docs/sql-ref-syntax-dml-merge-into.md
b/docs/sql-ref-syntax-dml-merge-into.md
new file mode 100644
index 000000000000..90731105d491
--- /dev/null
+++ b/docs/sql-ref-syntax-dml-merge-into.md
@@ -0,0 +1,223 @@
+---
+layout: global
+title: MERGE INTO
+displayTitle: MERGE INTO
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `MERGE INTO` statement merges a source table or query into a target table.
Rows of the
+target table are matched against the source using a merge condition. Target
rows are then
+updated or deleted, and new rows are inserted from the source, according to
the clauses that
+apply. All of these row-level changes are performed as a single atomic
operation.
+
+`MERGE INTO` is supported on tables backed by
+[Data Source V2](sql-data-sources-v2.html#row-level-dml) connectors that
support row-level operations.
+
+### Syntax
+
+```sql
+MERGE INTO target_table [ [ AS ] target_alias ]
+ USING { source_table | ( source_query ) } [ [ AS ] source_alias ]
+ ON merge_condition
+ [ WHEN MATCHED [ AND matched_condition ] THEN matched_action ] [ ... ]
+ [ WHEN NOT MATCHED [ BY TARGET ] [ AND not_matched_condition ] THEN
not_matched_action ] [ ... ]
+ [ WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN
not_matched_by_source_action ] [ ... ]
+
+matched_action
+ { DELETE | UPDATE SET * | UPDATE SET { column = value } [ , ... ] }
+
+not_matched_action
+ { INSERT * | INSERT ( column [ , ... ] ) VALUES ( value [ , ... ] ) }
+
+not_matched_by_source_action
+ { DELETE | UPDATE SET { column = value } [ , ... ] }
+```
+
+### Parameters
+
+* **target_table**
+
+ Specifies the table to be merged into, which may be optionally qualified
with a database name.
+ An optional alias may be provided with or without the `AS` keyword.
+
+ **Syntax:** `[ database_name. ] table_name [ [ AS ] alias ]`
+
+* **source_table** / **source_query**
+
+ The source of the merge, specified either as a table or as a parenthesized
query. An optional
+ alias may be provided with or without the `AS` keyword.
+
+* **merge_condition**
+
+ A boolean expression, introduced by the `ON` keyword, that determines how
rows from the source
+ match rows in the target. It controls which `WHEN` clauses apply to each
row.
+
+* **WHEN MATCHED [ AND matched_condition ] THEN matched_action**
+
+ Applies to rows that exist in both the source and the target (according to
`merge_condition`).
+ The optional `matched_condition` further restricts which matched rows the
clause applies to.
+ The `matched_condition` and the `matched_action` values can reference
columns of both the
+ target and the source tables.
+ The `matched_action` can be one of:
+ - `DELETE`: deletes the matched target row.
+ - `UPDATE SET *`: updates the matched target row with all source columns,
matched by name.
+ - `UPDATE SET column = value [ , ... ]`: updates the listed target columns
with the given values.
+
+* **WHEN NOT MATCHED [ BY TARGET ] [ AND not_matched_condition ] THEN
not_matched_action**
+
+ Applies to source rows that have no matching row in the target. `BY
TARGET` is optional and is
+ the default interpretation of `WHEN NOT MATCHED`. The optional
`not_matched_condition` further
+ restricts which unmatched source rows the clause applies to. The
`not_matched_condition` and the
+ `not_matched_action` values can reference columns of the source table only.
+ The `not_matched_action` can be one of:
+ - `INSERT *`: inserts a new target row using all source columns, matched
by name.
+ - `INSERT ( column [ , ... ] ) VALUES ( value [ , ... ] )`: inserts a new
target row, assigning
+ the given values to the listed columns.
+
+* **WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN
not_matched_by_source_action**
+
+ Applies to target rows that have no matching row in the source. The
optional
+ `not_matched_by_source_condition` further restricts which such target rows
the clause applies to.
+ The `not_matched_by_source_condition` and the
`not_matched_by_source_action` values can reference
+ columns of the target table only.
+ The `not_matched_by_source_action` can be one of:
+ - `DELETE`: deletes the unmatched target row.
+ - `UPDATE SET column = value [ , ... ]`: updates the listed target columns
with the given values.
+
+**Note:**
+- A `MERGE INTO` statement must contain at least one `WHEN` clause.
+- Multiple clauses of the same type may be specified. For a given row, the
clauses of the relevant
+ type are evaluated in the order they are written, and the first clause whose
condition is satisfied
+ is applied. When multiple clauses of the same type are specified, only the
last one may omit its
+ `AND` condition.
+- If a single target row matches more than one source row, the statement fails
with a
+ `MERGE_CARDINALITY_VIOLATION` error, since the row would otherwise be
updated or deleted more than
+ once. This check is skipped when there are no `WHEN MATCHED` clauses, or
when the only `WHEN MATCHED`
+ action is an unconditional `DELETE`, because in those cases the outcome does
not depend on the number
+ of matching source rows.
+
+### Examples
+
+The following examples assume that the `target` and `source` tables have
already been created and
+populated as shown below. Each example starts from this initial state.
+
+```sql
+SELECT * FROM target;
++--+------+-------+
+|pk|salary| dep|
++--+------+-------+
+| 1| 300| hr|
+| 2| 160| eng|
+| 5| 100|finance|
++--+------+-------+
+
+SELECT * FROM source;
++--+------+-----+
+|pk|salary| dep|
++--+------+-----+
+| 1| 350| hr|
+| 2| 200| eng|
+| 3| 120|sales|
++--+------+-----+
+```
+
+#### Update Matched Rows and Insert New Rows
+
+```sql
+MERGE INTO target t
+ USING source s
+ ON t.pk = s.pk
+ WHEN MATCHED THEN UPDATE SET *
+ WHEN NOT MATCHED THEN INSERT *;
+
+SELECT * FROM target;
++--+------+-------+
+|pk|salary| dep|
++--+------+-------+
+| 1| 350| hr|
+| 2| 200| eng|
+| 3| 120| sales|
+| 5| 100|finance|
++--+------+-------+
+```
+
+#### Conditional Delete, Update, and Insert
+
+```sql
+-- Delete matched rows whose source salary exceeds 300, update the remaining
matched rows,
+-- and insert source rows that do not match any target row.
+MERGE INTO target t
+ USING source s
+ ON t.pk = s.pk
+ WHEN MATCHED AND s.salary > 300 THEN DELETE
+ WHEN MATCHED THEN UPDATE SET salary = s.salary
+ WHEN NOT MATCHED THEN INSERT (pk, salary, dep) VALUES (s.pk, s.salary,
s.dep);
+
+SELECT * FROM target;
++--+------+-------+
+|pk|salary| dep|
++--+------+-------+
+| 2| 200| eng|
+| 3| 120| sales|
+| 5| 100|finance|
++--+------+-------+
+```
+
+#### Delete Target Rows Not Present in the Source
+
+```sql
+MERGE INTO target t
+ USING source s
+ ON t.pk = s.pk
+ WHEN MATCHED THEN UPDATE SET *
+ WHEN NOT MATCHED THEN INSERT *
+ WHEN NOT MATCHED BY SOURCE THEN DELETE;
+
+SELECT * FROM target;
++--+------+-----+
+|pk|salary| dep|
++--+------+-----+
+| 1| 350| hr|
+| 2| 200| eng|
+| 3| 120|sales|
++--+------+-----+
+```
+
+#### Update Target Rows Not Present in the Source
+
+```sql
+MERGE INTO target t
+ USING source s
+ ON t.pk = s.pk
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET dep = 'unassigned';
+
+SELECT * FROM target;
++--+------+----------+
+|pk|salary| dep|
++--+------+----------+
+| 1| 300| hr|
+| 2| 160| eng|
+| 5| 100|unassigned|
++--+------+----------+
+```
+
+### Related Statements
+
+* [INSERT TABLE statement](sql-ref-syntax-dml-insert-table.html)
+* [SELECT statement](sql-ref-syntax-qry-select.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 1e0ea4a2b8d6..222c507423f8 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -49,6 +49,7 @@ Data Manipulation Statements are used to add, change, or
delete data. Spark SQL
* [INSERT TABLE](sql-ref-syntax-dml-insert-table.html)
* [INSERT OVERWRITE
DIRECTORY](sql-ref-syntax-dml-insert-overwrite-directory.html)
+ * [MERGE INTO](sql-ref-syntax-dml-merge-into.html)
* [LOAD](sql-ref-syntax-dml-load.html)
### Data Retrieval Statements
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
