Skip to content

MERGE

Jump to bottom
Konstantin Triger edited this page Aug 18, 2019 · 3 revisions

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:

Inserting data returned from an OUTPUT clause

Note, the salesByProducts() returns sales for a specific day. Thus this MERGE 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

Examples

Basic SQL DML Statements

Advanced SQL DML Statements

Advanced Topics

Clone this wiki locally