How to use MERGE in LibreOffice Base?

Hi everyone, so I am trying to use the MERGE command but something is wrong with my syntax. I have read documentation here, here and there, but something is still missing.

MERGE INTO "T_Contacts"
USING "T_Villes"
ON "T_Contacts"."Ville"="T_Villes"."Ville"
WHEN MATCHED THEN UPDATE SET "T_Contacts"."ID_ville"="T_Villes"."ID"

and the error message:

4: Unexpected token: MERGE in statement [MERGE] ./connectivity/source/drivers/jdbc/Object.cxx:175

What is incorrect here?
Thank you for your help!

Base is not a database. It is a software to work with databases. The built-in database is HSQL version 1.8 of 2008 or so. When you create a new “Base database”, the document (which is just a zip archive) includes a database directory with the files of a HSQL database. This database is “installed” to a temporary directory when you start an editing session and it is wrapped back into the document when you finish an editing session. This implies a high risk of data loss as the database grows larger.
HSQL 1.8 is fully documented on this page: Chapter 9. SQL Syntax

A few days ago, I created a HSQLDB of version 2 connected with a Base document. HSQL2 supports MERGE as documented here: Chapter 5. Data Access and Change

See Apache OpenOffice Community Forum - [HSQL, Base, Basic] Macro Driven CSV Import - (View topic) which links to a downloadable zip archive with all ingredients.
Some time later I learned how to use UPDATE without MERGE in HSQL1. I attached an embedded HSQL to the same topic.

1 Like