First published on MSDN on Jul 16, 2018
SQL Server 2017 and Azure SQL Database introduced
native graph database capabilities
used to model many-to-many relationships. The first implementation of SQL Graph introduced support for edges to represent relationships, nodes to represent entities and a new MATCH predicate to support graph pattern matching and traversal.
We will be further expanding the graph database capabilities with several new features. In this blog we will discuss one of these features that is now available for public preview in Azure SQL Database,
MATCH support in MERGE DML for graph tables
.
The
MERGE
statement performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a target table based on differences between the target table and the source table. Using MATCH predicates in a MERGE statement is now supported on Azure SQL Database. That is, it is now possible to merge your current graph data (node or edge tables) with new data using the MATCH predicates to specify graph relationships in a single statement, instead of separate INSERT/UPDATE/DELETE statements.
In this post we will discuss few examples of how MATCH can be used in MERGE statement. For these examples, we have created
Customers
and
StockItems
node tables and
bought
edge table in the
WideWorldImporters
database.
Graph schema for MERGE examples: Customers bought StockItems
Merging node table data
A MERGE statement can be used to insert a new node record or update attributes on an existing record using a single statement (UPSERT). A MATCH predicate is not necessarily required for this scenario and hence, using merge to upsert node data was supported in previous releases. Here is an example:
WideWorldImporters
would like to create a new
Customers
node record when a customer buys a stockitem. If the customer already exists and a new
WebsiteURL
was provided, they would like to update the customer record with the new information. They can use the following MERGE DML on the
Customers
node table:
[code language=”sql”]
CREATE OR ALTER PROCEDURE dbo.mergeCustomerNode
@CustomerID BIGINT,
@CustomerName VARCHAR(100),
@CustomerWebsiteURL VARCHAR(1000)
AS
BEGIN
MERGE Nodes.Customers AS C
USING (SELECT @CustomerID, @CustomerName, @CustomerWebsiteURL)
AS T (CustomerID, CustomerName, CustomerWebsiteURL)
ON (C.CustomerID = T.CustomerID)
WHEN MATCHED THEN
UPDATE SET WebsiteURL = T.CustomerWebsiteURL
WHEN NOT MATCHED THEN
INSERT (CustomerID, CustomerName, WebsiteURL)
VALUES (T.CustomerID, T.CustomerName, T.CustomerWebsiteURL) ;
END
GO
[/code]
Merging edge table data
With this release, users can insert a new edge record or update or delete an existing one in a single statement using the MERGE DML. For example, continuing with the graph shown above, the following MERGE DML can be used to either insert a new
bought
edge when a
Customer
buys a
StockItem
, or if the edge already exists then update the value of
PurchasedCount
attribute on the existing edge record:
[code language=”sql”]
CREATE PROCEDURE dbo.mergeBoughtEdge
@CustomerID integer,
@ProductID integer,
@purchased_count integer
AS
BEGIN
MERGE Edges.bought
USING ((SELECT @CustomerID, @ProductID) AS T (CustomerID, ProductID)
JOIN Nodes.Customers ON T.CustomerID = Customers.CustomerID
JOIN Nodes.StockItems ON T.ProductID = StockItems.StockItemID)
ON MATCH (Customers-(bought)->StockItems)
WHEN MATCHED THEN
UPDATE SET PurchasedCount = PurchasedCount + @purchased_count
WHEN NOT MATCHED THEN
INSERT ($from_id, $to_id, PurchasedCount)
VALUES (Customers.$node_id, StockItems.$node_id, @purchased_count) ;
END
GO
[/code]
Let’s consider an example to see how the MERGE query shown above works. Here, we will pick StockItemID 226 (White chocolate snow balls 250g) and CustomerID 946 (David Safranek). The following query shows that David has bought two quantities of White chocolate snow balls.
[code language=”sql”]
SELECT CustomerName, StockItemName, PurchasedCount
FROM Nodes.Customers, Nodes.StockItems, Edges.bought
WHERE MATCH (Customers-(bought)->StockItems)
AND StockItemID = 226
AND CustomerID = 946
[/code]
CustomerName | StockItemName | PurchasedCount |
David safranek | White chocolate snow balls 250g | 2 |
Now, assume that David again bought 2 quantities of White chocolate snow balls,
WideWorldImporters
will execute the
dbo.mergeBoughtEdge
stored procedure to run the MERGE statement on edge table for this transaction as follows:
[code language=”sql”]
exec dbo.mergeBoughtEdge 946, 226, 2
GO
[/code]
After the merge statement completes, if we run the SELECT statement shown above again, we get following result. Note that PurchasedCount for White chocolate snow balls has now been updated to 4. Since, the edge already existed, WHEN MATCHED section was evaluated to true and update statement was executed to update the PurchasedCount value on the edge.
CustomerName | StockItemName | PurchasedCount |
David safranek | White chocolate snow balls 250g | 4 |
Merge with additional search condition on WHEN MATCHED
Here is an example of MERGE with an extra search condition in the WHEN MATCHED clause. The WHEN MATCHED clause is executed only if the pattern in MATCH is true and T.Purchased_Count is not null.
[code language=”sql”]
CREATE OR ALTER PROCEDURE dbo.mergeBoughtEdgeWithCondition
@CustomerID Integer,
@ProductID integer,
@purchased_count integer
AS
BEGIN
MERGE Edges.bought
USING ((SELECT @CustomerID, @ProductID, @purchased_count) AS T (CustomerID, ProductID, Purchased_Count)
JOIN Nodes.Customers ON T.CustomerID = Customers.CustomerID
JOIN Nodes.StockItems ON T.ProductID = StockItems.StockItemID)
ON MATCH (Customers-(bought)->StockItems)
WHEN MATCHED AND T.Purchased_Count > 0 THEN
UPDATE SET PurchasedCount = PurchasedCount + T.Purchased_Count
WHEN NOT MATCHED THEN
INSERT ($from_id, $to_id, PurchasedCount)
VALUES (Customers.$node_id, StockItems.$node_id, T.Purchased_Count) ;
END
GO
[/code]
To run the examples shown here, download and restore the WideWorldImporters database backup (follow these
setup instructions
) and then run the following scripts to create and populate the node and edge tables:
Conclusion
UPSERT scenarios on graph tables can now be performed through a single MERGE statement. In this blog we discussed some of the scenarios where MERGE can be used. It can especially be useful when you would like to insert/update edge data depending on whether or not an edge already exists between two nodes.
If you have feedback on this feature, please email us at sqlgraph@service.microsoft.com.