2

Is there a SQL statement (or atomic sequence of statements) supported by both MySQL and HSQLDB to insert values if they aren't already there?

I'm working on an app that uses MySQL as its production database and HSQLDB for unit tests; I'd like to have a single "initial data import when the tables are empty" script.

MySQL supports INSERT IGNORE, REPLACE INTO and INSERT INTO ... ON DUPLICATE KEY UPDATE ..., but HSQLDB doesn't; conversely, HSQLDB supports MERGE but MySQL doesn't.

Solal Pirelli
  • 1,199
  • 9
  • 21
  • Look at accepted answer [here](https://stackoverflow.com/questions/15252213/sql-standard-upsert-call), I think it is the only way to go, if you do not want to preprocess scripts with some other script. – Dmitry V. May 31 '15 at 12:20

2 Answers2

2

HSQLDb from version 2.3.4 adds support for insert ignore.

http://hsqldb.org/

Version 2.3.4 added the UUID type for columns, SYNONYM for tables and functions, PERIOD predicates, and auto-updated TIMESTAMP columns on row updates. Other new features included the ability to cancel long-running statements from JDBC as well as from admin sessions, and UTF-16 file support for text table sources, in addition to 8-bit text files. MySQL compatibility for REPLACE, INSERT IGNORE and ON DUPLICATE KEY UPDATE statements.

And

http://hsqldb.org/doc/guide/guide.pdf (page 260).

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. With multi-row inserts, 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.

Alan Hay
  • 22,665
  • 4
  • 56
  • 110
0

If someone still has this problem you can enable syntax support for MySQl by adding the following to your script

SET DATABASE SQL SYNTAX MYS TRUE
David AJ
  • 29
  • 1
  • 4