Search code examples
mergehsqldb

HSQLDB merge into can neither insert nor update

I'm trying to use MERGE INTO to save or update HSQLDB table. I ran into an issue of inserting new record when NOT MATCHED.

Steps of reproduce the issue

  1. Create table

CREATE TABLE user ( id INT, name VARCHAR(16), address VARCHAR(64) );

  1. MERGE INTO

MERGE INTO user u1 USING (select id from user where id=1) u2
ON u1.id = u2.id
WHEN MATCHED THEN UPDATE SET u1.name='name', u1.address='address'
WHEN NOT MATCHED THEN INSERT (id, name, address) values(1, 'name', 'address')

If there is no record with id:1, above statement doesn't nothing, "Updated Rows: 0".

If insert one record

INSERT INTO user values(1, 'Y', 'B');

above MERGE INTO statement can update the existing record.

Anyone knows what's the issue with INSERT WHEN NOT MATCHED? Is there the third case other than "WHEN MATCHED" and "WHEN NOT MATCHED"?

Thanks.

Solution

  • The problem is in this clause:

    USING (select id from user  where id=1) u2

    When there is no record in the table, there is no row generated by the USING clause. So there is nothing to insert or update the existing rows with.

    You need to rewrite it so the USING clause generates a row with data.

    For example:

    USING (values(1)) u2(id)