The Merge Statement in SQL

I needed some place to write this down. I don't use it often (or at all) and it has too many advantages for me to ignore it.

Interestically enough, the merge statement sometimes seems to have a huge performance advantage when needing to perform operations in bulk.

But this effect can be absent, when attempting merge statements on individual entries.

MERGE INTO target_table
USING source_table
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE >insert_condition>;

MERGE INTO items gemc
USING (select orderid from orders where customer=5300) geme
ON (gemc.orderid = geme.orderid and gemc.articlenr = 120)
WHEN MATCHED THEN
    UPDATE SET gemc.coupon = 'AFEY12',
               gemc.amount = gemc.amount + 1
    WHERE gemc.orderid = geme.orderid
    AND gemc.articlenr = 120
WHEN NOT MATCHED THEN
    INSERT(gemc.itemid, gemc.orderid, gemc.articleid, gemc.coupon, gemc.amount, gemc.creation)
    VALUES(seq_items.nextval, geme.orderid, 120, 'AFEY12', 1, sysdate);

References

Wikipedia - Merge(SQL)

https://en.wikipedia.org/wiki/Merge_(SQL)

Oracle Tutorial - Oracle Merge

https://www.oracletutorial.com/oracle-basics/oracle-merge/

Linus Torvals on Debuggers

I happen to come across this priceless piece of text¹ from Linus Torvals, that I feel should have a mention here.

References

[1] lwn.net - Re: Availability of kdb

https://lwn.net/2000/0914/a/lt-debugger.php3

AssertJ and how your IDE can help

So I am using AssertJ¹ to verify results of my Unit Tests.

I also (after the advice of a colleague) installed a Plugin into my IntelliJ IDEA called Concise AssertJ Optimizing Nitpicker ​(Cajon)​²

So I needed to verify the equivalence of an object. I wanted to make sure it was the exact same object I was expecting.

In my naivite I tried something like this.

assertThat(getItems()).hasSize(1);
Optional<Item> first = CollectionUtilities.getFirst(getItems());
assertThat(first).isPresent();
assertThat(first.get() == originalitem).isTrue();

My IDE complained that "Moving binary expression out of assertThat() would be more meaningful".

I completely agreed with that.

assertThat(getItems()).hasSize(1);
Optional<Item> first = CollectionUtilities.getFirst(getItems());
assertThat(first).isPresent();
assertThat(first.get()).isSameAs(originalitem);

Then my IDE commplained that "Moving get() expression out of assertThat() would be more concise", which of course is obvious.

assertThat(getItems()).hasSize(1);
Optional<Item> first = CollectionUtilities.getFirst(getItems());
assertThat(first).isPresent();
assertThat(first).containsSame(originalitem);

Then, of course, Multiple assertThat() statements can be joined together

assertThat(getItems()).hasSize(1);
Optional<Item> first = CollectionUtilities.getFirst(getItems());
assertThat(first).isPresent().containsSame(originalitem);

Which means that Implicit isPresent() assertion is covered by containsSame()

assertThat(getItems()).hasSize(1);
Optional<Item> first = CollectionUtilities.getFirst(getItems());
assertThat(first).containsSame(originalitem);

And that's what I ended up with.

Truly, sometimes my IDE has some good ideas.

Alternative

Of course, there are easier methods, but I wanted to verify that it is the exact same instance.

Otherwise, I could have simply done something like:

assertThat(getItems()).containsExactly(originalitem);

Still not entirely sure that this is not better, despite not an exact match check.

References

[1] AssertJ

https://assertj.github.io/doc/

[2] Concise AssertJ Optimizing Nitpicker ​(Cajon)​

https://plugins.jetbrains.com/plugin/12195-concise-assertj-optimizing-nitpicker-cajon-/

Background : How we got the generics we have

So I came across this text¹ on the background of the Generics in the Java Platform.

And I thought it worth my while to put it in here in my blog for easy reference.

References

[1] Brian Goetz - Background : How we got the generics we have

https://cr.openjdk.java.net/~briangoetz/valhalla/erasure.html