@Test
  public void testMergeDeleteUpdate() throws Exception {
    int[][] sourceVals = {{2,2},{4,44},{5,5},{11,11}};
    runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(sourceVals));
    int[][] targetVals = {{2,1},{4,3},{5,6},{7,8}};
    runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(targetVals));
    String query = "merge into " + Table.ACIDTBL +
        " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " +
        "WHEN MATCHED and s.a < 5 THEN DELETE " +
        "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " +
        "WHEN NOT MATCHED THEN INSERT VALUES(s.a, s.b) ";
    runStatementOnDriver(query);

    List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b");
    int[][] rExpected = {{5,6},{7,8},{11,11}};
    Assert.assertEquals(stringifyValues(rExpected), r);
  }
https://www.linkedin.com/pulse/how-load-slowly-changing-dimension-type-2-using-oracle-padhy
Same as testMergeType2SCD01 but with a more intuitive "source" expression
ql/src/test/org/apache/hadoop/hive/ql/TestTxnCommands.java