Can I use MERGE INTO to simulate "upsert" in Apache Derby?

Question

We're using Derby and have a lot of code which goes like this:

try (ResultSet rs = executeQuery(...)) {
    if (rs.next()) {
        updateRowSet(rs, ...);
        rs.updateRow();
    } else {
        executeUpdate(...);
    }
}

In the past, we were searching for a way to do this logic server-side, and found that some databases supported an "upsert" (update or insert) operation. Derby had a feature request for MERGE INTO which was supposedly the SQL:2003 standard way of doing this, so we sat and watched the ticket, and much time passed.

Derby 10.11 finally added MERGE INTO. Nobody has had time to go through and update the code to use it yet, but on reading their documentation, all their examples show merging from one table to another. But hang on, our data isn't in a table yet!

I know I could put it in a table, but then it is multiple queries again, which completely defeats the point of using it.

I'm sure it is possible to do it without putting it into a table, but because the documentation does not show a single example, I'm not sure how to proceed.

Here is what I have been trying:

try (PreparedStatement ps = connection.prepareStatement(
        "MERGE INTO things AS target " +
        // Awkward point 1:
        // It wants a "source" table, but I don't have one.
        // So I thought I would try to use the same table with
        // another name.
        "  USING things AS source ON target.id = ?" +
        "  WHEN MATCHED THEN" +
        "    UPDATE SET data = ?" +
        "  WHEN NOT MATCHED THEN" +
        "    INSERT (id, data) VALUES (??, ?)"))
{
    ps.setLong(1, id);
    ps.setBinaryStream(2, data);
    ps.setLong(3, id);
    // Awkward point 2:
    // Passing an InputStream into a query as two
    // parameters.
    ps.setBinaryStream(4, data);
    ps.execute();
}

This doesn't appear to do any of the inserts, but also doesn't give an error, so I have absolutely nothing to go on.

Answer 1

Sharing it for all sad people that still use derby :) So I solved it with help of merge into statement( https://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html) in that way:

MERGE INTO foo
USING SYSIBM.SYSDUMMY1
ON foo.id = '1' AND foo.language = 'en'
WHEN MATCHED THEN
  UPDATE SET name = 'name2', image = 'someImgUrl2'
WHEN NOT MATCHED THEN
  INSERT (id, name, language, image)
  VALUES ('1', 'name1', 'en', 'someImgUrl1')

Where foo is the table where u want to upsert row and SYSIBM.SYSDUMMY1 derby dummy table that has only 1 useless row (btw it doesn't work with one of my regular table that has multiple rows)

As u might understand it's more like workaround but better than nothing to achive upsert aim.