0

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.

Mikey
  • 11
  • 1
  • "*Please note that I am using quite old HSQLDB engine*" then why don't you upgrade? And which version exactly are you using? –  Nov 29 '15 at 22:08
  • If I could only figure out that one... Looks like it came with Java Runtime Environment 1.6.0_20. I don't want to make any upgrades or changes of the engines as there is quite a bit of code already written which works only with this particular version of OpenOffice. Whenever I am trying to use a MERGE, the command processor throws error at it "Unexpected token: MERGE in statement [MERGE]". I am deducting that it just does not recognise this command, therefore hsqldb engine is quite old one. – Mikey Nov 29 '15 at 22:27
  • 1
    I think you can use the `split database` connection to HSQLDB 2.3.3 even with OO 3.3. See https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=17567 – fredt Nov 29 '15 at 23:13
  • I am already using split database engine from OO interface. By reading this link a bit turns out that there are some possible compatibility issues and I really don't want to get into long time re-development of this old project. – Mikey Nov 30 '15 at 23:11
  • The version that ships with OpenOffice is HSQLDB 1.8; documentation is at http://www.hsqldb.org/doc/1.8/guide/. I strongly suspect what you want to do cannot be done entirely in the SQL available. Can you write a macro in another language to test the destination table, then compose the appropriate SQL (UPDATE or INSERT) and execute it? – Lyrl Dec 01 '15 at 21:46
  • Thanks Lyrl. I will check it and see what I can find there. – Mikey Dec 01 '15 at 21:47
  • I don't know why I couldn't see your whole message before. Anyways... The whole project is using macros in OO. Initially I was using macros to do checks if record does or does not exist in destination table and based on that I was creating proper SQL command INSERT/UPDATE. The thing is that copying circa 3000 records right now takes around... 2min. If I execute one SQL INSERT command for all the records it takes less than a second. That is why I am looking for SQL solution. – Mikey Dec 01 '15 at 23:16

1 Answers1

0

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>
fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks for snippets. First one needs correcting to the following line to make it working: INSERT INTO dest (SELECT * FROM SOURCE WHERE EXCEPT (SELECT * FROM source JOIN dest ON source.id = dest.id WHERE )); With the update I could not make it working apart only for one column as this HSQLDB does not support multi-column syntax. Nonetheless it is still a major step forward! – Mikey Dec 06 '15 at 01:33