MERGE
statement to update data in a table based on values matched from another table.MERGE
StatementINSERT
, UPDATE
, and DELETE
statement individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table.MERGE
statement that allows you to perform three actions at the same time. The following shows the syntax of the MERGE
statement:MERGE
clause.merge_condition
determines how the rows from the source table are matched to the rows from the target table. It is similar to the join condition in the join clause. Typically, you use the key columns either primary key or unique key for matching.merge_condition
results in three states: MATCHED
, NOT MATCHED
, and NOT MATCHED BY SOURCE
.MATCHED
: these are the rows that match the merge condition. In the diagram, they are shown as blue. For the matching rows, you need to update the rows columns in the target table with values from the source table.NOT MATCHED
: these are the rows from the source table that does not have any matching rows in the target table. In the diagram, they are shown as orange. In this case, you need to add the rows from the source table to the target table. Note that NOT MATCHED
is also known as NOT MATCHED BY TARGET
.NOT MATCHED BY SOURCE
: these are the rows in the target table that does not match any rows in the source table. They are shown as green in the diagram. If you want to synchronize the target table with the data from the source table, then you will need to use this match condition to delete rows from the target table.MERGE
statement examplesales.category
and sales.category_staging
that store the sales by product category.sales.category
(target table) with the values from the sales.category_staging
(source table), you use the following MERGE
statement:category_id
columns in both tables as the merge condition.sales.category_staging
table matches with the rows from the target table, therefore, the MERGE
statement updates the values in category name and amount columns in the sales.category
table.sales.category_staging
table do not exist in the sales.category
table, so the MERGE
statement inserts these rows into the target table.sales.category
table does not exist in the sales.sales_staging
table, therefore, the MERGE
statement deletes this row.sales.category
table is fully synchronized with the data in the sales.category_staging
table.MERGE
statement to make changes in a table based on matching values from another table.AND NOT target_table.column_x = value
. Doing so may return unexpected and incorrect results.WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1
may cause the statement to fail because Col1
in the source table is inaccessible.MERGE CLAUSE in Azure Synapse Analytics | Supported TARGE distribution table | Supported SOURCE distribution table | Comment |
---|---|---|---|
WHEN MATCHED | All distribution types | All distribution types | |
NOT MATCHED BY TARGET | HASH | All distribution types | Use UPDATE/DELETE FROM…JOIN to synchronize two tables. |
NOT MATCHED BY SOURCE | All distribution types | All distribution types |
SELECT @@VERSION
. If the fix has not been applied, manually pause and resume your Synapse SQL pool to get the fix.WITH <common table expression>
clause to filter out rows from the source or target tables. This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. We recommend that you avoid using this method or test thoroughly before implementing it.OPTION (<query_hint>)
clause in the MERGE statement. We recommend that you do not use the hash join as a query hint for MERGE statements because this join type does not use indexes.ON <merge_search_condition>
clause and in the the WHEN
clauses of the MERGE statement. For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.TEMPLATE
and specify the PARAMETERIZATION FORCED
query hint in the plan guide.PARAMETERIZATION
option is a database-level setting and affects how all queries against the database are processed.OPENROWSET(BULK…)
clause as the table source. By doing so, the entire file is processed in a single batch.OPENROWSET(BULK…)
clause to specify how the source data file is sorted.ProductInventory
table in the AdventureWorks2012 sample database, daily, based on orders that are processed in the SalesOrderDetail
table. The Quantity
column of the ProductInventory
table is updated by subtracting the number of orders placed each day for each product in the SalesOrderDetail
table. If the number of orders for a product drops the inventory level of a product to 0 or less, the row for that product is deleted from the ProductInventory
table.SalesReason
table in the AdventureWorks2012 database by either updating or inserting rows. When the value of NewName
in the source table matches a value in the Name
column of the target table, (SalesReason
), the ReasonType
column is updated in the target table. When the value of NewName
doesn't match, the source row is inserted into the target table. The source table is a derived table that uses the Transact-SQL table value constructor to specify multiple rows for the source table. For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). The example also shows how to store the results of the OUTPUT clause in a table variable. And, then you summarize the results of the MERGE statement by running a simple select operation that returns the count of inserted and updated rows.Quantity
column of the ProductInventory
table in the AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail
table. The example captures the updated rows and inserts them into another table that's used to track inventory changes.Person
and City
and an edge table livesIn
. You use the MERGE statement on the livesIn
edge and insert a new row if the edge doesn't already exist between a Person
and City
. If the edge already exists, then you just update the StreetAddress attribute on the livesIn
edge.