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.

Possibly useful related questions: http://stackoverflow.com/questions/11216067/what-is-using-in-sql-server-2008-merge-syntax and http://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server — Bryan Pendleton, Jan 12 '16 at 05:49
I don't think this is possible with Derby. It does not allow constant values as the "source" of a merge statement. — , Jan 12 '16 at 07:14

score 5 · Answer 1 · edited Aug 17 '18 at 08:36

5

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.

edited Aug 17 '18 at 08:36

eis

51,991
13
150
199

answered Jul 18 '18 at 16:12

Dzmitry Hubin

1,091
12
14

having `CREATE TABLE foo(id VARCHAR(2), name VARCHAR(48), language VARCHAR(48), image VARCHAR(48))` and using this I get "java.sql.SQLSyntaxErrorException: Column 'FOO.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'FOO.ID' is not a column in the target table.". Using derby 10.14.2.0. – eis Aug 17 '18 at 08:30
1

ah, found the problem! You have "foo AS target" but you still use 'foo' in this. Fixing and upvoting. – eis Aug 17 '18 at 08:35
also, it seems that when using this, column default values are not honored in insert part like in regular insert statements. That seems to be a bug in Derby. – eis Aug 17 '18 at 10:50
Thanks for your fix here. One minor glitch I experienced was that Apache Derby threw "ERROR 42821: Columns of type 'BIGINT' cannot hold values of type 'CHAR'" if the numbers were input with quotes '1' rather than simply 1. Removing the quotes from the numbers fixed it for me. – keithphw Jan 12 '22 at 05:08

score 2 · Answer 2 · answered Mar 13 '16 at 06:27

The following worked for me with Apache Derby 10.12.1.1:

merge into FOO
using RANDOM_TABLE
on FOO.guid = 'qwerty'
when matched then
    update set guid = 'qwerty'
when not matched then
    insert (guid) values('qwerty')

Here FOO is my target table to do the upsert into and RANDOM_TABLE is any other table in my database. The value 'qwerty' is my data and the unique key. In this example FOO only has a single column, but it should work to just add more columns to the insert and update respectively.

I find this syntax quite inelegant, but at least it does seem to avoid executing two separate statements to do the job.

Strange, I just get `Error code 30000, SQL state 42X01: Syntax error: Encountered "INTO" at line 7, column 12.` — Hooli, Jul 29 '16 at 18:31

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

2 Answers2

Linked