MERGE |
Fusion |
---|---|
Microsoft SQL Server |
Syntaxe
[ WITH common_table_expression [,...n] ] MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] target_table [ WITH ( merge_hint ) ] [ [ AS ] table_alias ] USING table_source ON merge_search_condition [ WHEN MATCHED [ AND clause_search_condition ] THEN merge_matched ] [ ...n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND clause_search_condition ] THEN merge_not_matched ] [ WHEN NOT MATCHED BY SOURCE [ AND clause_search_condition ] THEN merge_matched ] [ ...n ] [ output_clause ] [ OPTION ( query_hint [ ,...n ] ) ] ; target_table ::= { [ database_name . schema_name . | schema_name . ] target_table } merge_hint::= { { [ table_hint_limited [ ,...n ] ] [ [ , ] INDEX ( index_val [ ,...n ] ) ] } } table_source ::= { table_or_view_name [ [ AS ] table_alias ] [ tablesample_clause ] [ WITH ( table_hint [ [ , ]...n ] ) ] | rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [ ,...n ] ) ] | user_defined_function [ [ AS ] table_alias ] | OPENXML openxml_clause | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | joined_table | pivoted_table | unpivoted_table } merge_search_condition ::= search_condition merge_matched::= { UPDATE SET set_clause | DELETE } set_clause::= SET { column_name = { expression | DEFAULT | NULL } | { udt_column_name.{ { property_name = expression | field_name = expression } | method_name ( argument [ ,...n ] ) } } | column_name { .WRITE ( expression , @Offset , @Length ) } | @variable = expression | @variable = column = expression | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression } [ ,...n ] merge_not_matched::= { INSERT [ ( column_list ) ] { VALUES ( values_list ) | DEFAULT VALUES } } clause_search_condition ::= search_condition search condition ::= MATCH(graph_search_pattern) | search_condition_without_match | search_condition AND search_condition search_condition_without_match ::= { [ NOT ] predicate | ( search_condition_without_match ) [ { AND | OR } [ NOT ] { predicate | ( search_condition_without_match ) } ] [ ,...n ] predicate ::= { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression | string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL | CONTAINS ( { column | * } , 'contains_search_condition' ) | FREETEXT ( { column | * } , 'freetext_string' ) | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { ALL | SOME | ANY} ( subquery ) | EXISTS ( subquery ) } graph_search_pattern ::= { node_alias { { <-( edge_alias )- } | { -( edge_alias )-> } node_alias } } node_alias ::= node_table_name | node_table_alias edge_alias ::= edge_table_name | edge_table_alias output_clause::= { [ OUTPUT dml_select_list INTO { @table_variable | output_table } [ (column_list) ] ] [ OUTPUT dml_select_list ] } dml_select_list::= { column_name | scalar_expression } [ [AS] column_alias_identifier ] [ ,...n ] column_name ::= { DELETED | INSERTED | from_table_name } . { * | column_name } | $action |
Description
Cette instruction permet d'effectuer des opérations d'insertions, de mise à jour ou de suppression dans une table destinataire basé sur le résultat d'un jointure d'une table source.
Dernière mise à jour : Vendredi, le 19 Juin 2020