HSQLDB insert if doesn't exists, update if exists

Question

I have a problem with my database based on HSQLDB (OpenOffice 3.3.0). I am trying to create SQL command that will select rows with specified columns from source table and copy it across to the destination table keeping in mind that if record does not exist in destination table, it should get created and if it does exist it should get updated. Please note that I am using quite old HSQLDB engine which does not support MERGE command.

Thanks.

Answer 1

With the old HSQLDB 1.8, you need to perform an INSERT and an UPDATE.

INSERT INTO dest (SELECT * FROM SOURCE WHERE <your condition> EXCEPT (SELECT * FROM source JOIN dest ON source.id = dest.id WHERE <your condition>));

UPDATE dest SET (col1, col2) = (SELECT coln, colm FROM source WHERE source.id = dest.id) WHERE <your condition>