-
Notifications
You must be signed in to change notification settings - Fork 10
MERGE
MERGE
is a very powerfull statement used to make changes in one table based on values matched from another. Here can be found an introduction to MERGE in addition ot Oracle docs and SQL Server docs. And yes, it's part of the ANSI SQL.
While the practical use case might not be clear at the first glance, I discovered
MERGE
for myself for UPSERT and batch updates happening on a schedule. The idea is that the "source" is a dynamic sub query, containing all the rows I want to look on in the "target". Then I can process all of them in a single pass. Consider the following examples:
Note, the
salesByProducts()
returns sales for a specific day. Thus thisMERGE
can run daily.
// Arguments are automatically captured and passed in via JPA's Query.setParameter()
String orderDate; // passed by an external parameter
FluentQuery query = FluentJPA.SQL(() -> {
// returns an entity!
SalesOrderDetail sales = salesByProducts(orderDate);
// previous result is an argument for the next function
Change change = updateInventoryWithSales(sales);
trackNoInventory(change);
});
...
// the result is SalesOrderDetail since the SELECTed columns are aliased to its fields
private static SalesOrderDetail salesByProducts(String orderDate) {
return subQuery((SalesOrderDetail sod,
SalesOrderHeader soh) -> {
// since the function returns SalesOrderDetail, alias
// SELECTed columns to SalesOrderDetail's fields (type safety is kept)
Product product = alias(sod.getProduct(), SalesOrderDetail::getProduct);
int orderQty = alias(SUM(sod.getOrderQty()), SalesOrderDetail::getOrderQty);
SELECT(product, orderQty);
FROM(sod).JOIN(soh)
.ON(sod.getSalesOrderID() == soh.getSalesOrderID()
&& soh.getOrderDate() == orderDate);
GROUP(BY(product));
});
}
private static Change updateInventoryWithSales(SalesOrderDetail order) {
return subQuery((ProductInventory inv) -> {
ProductInventory deleted = DELETED();
MERGE().INTO(inv).USING(order).ON(inv.getProduct() == order.getProduct());
// Non foreign key Object JOIN -----------------^^^^^^^^
WHEN_MATCHED_AND(inv.getQuantity() - order.getOrderQty() <= 0).THEN(DELETE());
WHEN_MATCHED().THEN(MERGE_UPDATE().SET(() -> {
inv.setQuantity(inv.getQuantity() - order.getOrderQty());
}));
// since the function returns Change, alias
// OUTPUTed columns to Change's fields
MergeAction action = alias($action(), Change::getAction);
int productID = alias(deleted.getProduct().getProductID(), Change::getProductID);
OUTPUT(action, productID);
});
}
private static void trackNoInventory(Change change) {
subQuery((ZeroInventory zi) -> {
INSERT().INTO(viewOf(zi, ZeroInventory::getDeletedProductID,
ZeroInventory::getRemovedOnDate));
SELECT(change.getProductID(), GETDATE());
FROM(change);
WHERE(change.getAction() == MergeAction.DELETE);
});
}
Bonuses to top workers (first example)
Citing Oracle: The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step.
Like a previous example, this query can run monthly.
int threshold; // passed by an external parameter
FluentQuery query = FluentJPA.SQL((Bonus bonus1) -> {
Bonus bonus = alias(bonus1, "D");
Employee empFromDep80 = alias(employeesFromDepartment(80), "S");
MERGE().INTO(bonus).USING(empFromDep80).ON(bonus.getEmployee() == empFromDep80);
// Uses @JoinColumn to resolve the association --------------^^^^^^
WHEN_MATCHED().THEN(() -> {
MERGE_UPDATE().SET(() -> {
bonus.setBonus(bonus.getBonus() + empFromDep80.getSalary() * .01f);
});
DELETE();
WHERE(empFromDep80.getSalary() > threshold);
});
WHEN_NOT_MATCHED().THEN(() -> {
MERGE_INSERT(bonus.getEmployee(), bonus.getBonus());
VALUES(row(empFromDep80.getId(), empFromDep80.getSalary() * .01f));
WHERE(empFromDep80.getSalary() <= threshold);
});
});
private static Employee employeesFromDepartment(int num) {
return subQuery((Employee emp) -> {
SELECT(emp);
FROM(emp);
WHERE(emp.getDepartment().getId() == num);
});
}
Getting Started
- Introduction
- Setup
- Data Types
- Entities & Tuples
- Sub Queries
- JPA Integration
- Java Language Support
- Directives
- Library
- Returning Results
- JPA Repositories
Examples
Basic SQL DML Statements
Advanced SQL DML Statements
- Common Table Expressions (WITH Clause)
- Window Functions (OVER Clause)
- Aggregate Expressions
- MERGE
- Temporal Tables
Advanced Topics