Skip to content
Toni Müller edited this page Sep 27, 2022 · 3 revisions

No such statement in SQL standard, but it's supported in several implementations:

BigQuery:

MERGE [INTO] table_name [[AS] alias]
  USING {table_name | query}
  ON condition
  when_clause...

when_clause:
    WHEN MATCHED [AND condition] THEN action
  | WHEN NOT MATCHED [BY TARGET] [AND condition] THEN action
  | WHEN NOT MATCHED BY SOURCE [AND condition] THEN action

action:
    UPDATE SET set_clause_list
  | DELETE
  | INSERT [columns_list] {VALUES values_list | ROW}

DB2:

MERGE INTO table_name [correlation_clause]
  USING table_name
  ON condition
  when_clause...
  [ELSE IGNORE]
  [WITH {RR | RS | CS | UR}

when_clause:
  WHEN [NOT] MATCHED [AND condition] THEN action

action:
    UPDATE SET set_clause_list
  | DELETE
  | INSERT [columns_list] VALUES values_list

Hive:

MERGE INTO table_name [[AS] alias]
  USING expr [[AS] alias]
  ON condition
  WHEN [NOT] MATCHED [AND condition] THEN action

action:
    UPDATE SET set_clause_list
  | DELETE
  | INSERT VALUES value_list}

MariaDB:

No support for MERGE

MySQL:

No support for MERGE

N1QL:

MERGE INTO table_name [[AS] alias]
  [use_index_clause]
  USING expr [[AS] alias] [hints]
  ON [[PRIMARY] KEY] condition
  when_clause...
  [limit_clause]
  [returning_clause]

use_index_clause:
  USE INDEX "(" index_ref [","...] ")"

when_clause:
    WHEN MATCHED THEN action [unset_clause] [where_clause]
  | WHEN MATCHED THEN action [where_clause]
  | WHEN NOT MATCHED THEN action [where_clause]

action:
  | UPDATE SET set_clause_list
  | DELETE
  | INSERT expr
  | INSERT "(" [KEY] key ["," [VALUE] value] ["," [OPTIONS] options] ")"

PL/SQL:

MERGE [hint] INTO table_name [[AS] alias]
  USING {table_name | query} [[AS] alias]
  ON "(" condition ")"
  when_clause...
  [error_logging_clause]

when_clause:
    WHEN MATCHED THEN action [where_clause] [DELETE where_clause]
  | WHEN NOT MATCHED THEN action [where_clause]

action:
    UPDATE SET set_clause_list
  | INSERT columns_list VALUES values_list

PostgreSQL:

No support for MERGE

Redshift:

No support for MERGE

SingleStoreDB:

No support for MERGE

Snowflake:

MERGE INTO table_name
  USING table_name ON where_clause
  when_clause...

when_clause:
    WHEN MATCHED [AND predicate] THEN update_delete 
  | WHEN NOT MATCHED THEN INSERT [col_list] VALUES values_list

update_delete:
    UPDATE SET set_clause_list
  | DELETE

Spark:

No support for MERGE

SQLite:

No support for MERGE

Transact-SQL:

[WITH common_table_expression ["," ...]]
MERGE [TOP (expression) [PERCENT]] [INTO] table_name
  [WITH (merge_hint)] [[AS] alias]
  USING table_source [[AS] alias]
  ON condition
  when_clause...
  [output_clause]
  [OPTION (query_hint [","...])]

when_clause:
  WHEN [NOT] MATCHED [BY TARGET | BY SOURCE] [AND condition] THEN action

action:
    UPDATE SET set_clause_list
  | DELETE
  | INSERT column_list {VALUES values_list | DEFAULT VALUES}

Trino:

No support for MERGE