From:
HyperSQL User Guide
HyperSQL Database Engine 2.4.0
Chapter 12. Compatibility With Other DBMS :
HyperSQL supports and translates INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of INSERT into predictable and error-free operations.
When INSERT IGNORE is used, if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, that row is not inserted. The rest of the rows are then inserted only if there is no other violation such as long strings or type mismatch, otherwise the appropriate error is returned.
When REPLACE or ON DUPLICATE KEY UPDATE is used, the rows that need replacing or updating are updated with the given values. This works exactly like an UPDATE statement for those rows. Referential constraints and other integrity checks are enforced and update triggers are activated. The row count returned is simply the total number of rows inserted and updated.
However when I try
REPLACE INTO my_table (my_id, my_int) VALUES (1, 2);
I get
unexpected token: REPLACE required: INSERT
Why is that?
I suggest that you need to enable MySQL compatibility mode in order to get MySQL-specific commands like REPLACE to work. From Chapter 7 of the HSQL documentation:
In MySQL syntax compatibility mode, HyperSQL supports INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of the INSERT statement.
The key point here is that it MySQL syntax compatibility mode needs to be turned on. Following the link to Chapter 12 which you posted in your question we find:
Use SET DATABASE SQL SYNTAX MYS TRUE or the equivalent URL property sql.syntax_mys=true to enable support for AUTO_INCREMENT and TEXT data types and several other types. These type definitions are translated into HyperSQL equivalents.
So the documentation is giving us two ways to enable MySQL compatibility mode. One we can execute directly from the HSQL console:
SET DATABASE SQL SYNTAX MYS TRUE
The other, probably the one to use for development purposes, is to add the following to the connection string:
sql.syntax_mys=true
Once you have MySQL compatibility mode enabled, REPLACE should work without error.