How to know if Hsqldb "MERGE INTO" has made an insert or an update

Question

Is it possible that a procedure using MERGE INTO returns the status of the action performed (inserted or updated).

I use Hsqldb 2.5.0.

Answer 1

It's not possible.

You need to execute a SELECT statement to count the affected rows before the MERGE. The first example given for MERGE in the Guide updates one row and inserts two rows:

http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_merge_statement

You can execute the SELECT statement below to get the number of rows to be updated:

SELECT COUNT(*) FROM t WHERE t.id in (1, 14, 5)