On Sun, 4 Apr 2010, Arne Vajh?j wrote:
On 04-04-2010 20:12, Lew wrote:
I hear good things about H2.
;ALLOW_LITERALS=NONE in the connection URL certainly is nice !
H2 supports the rather useful MERGE INTO command, which apart from the
first word looks identical to INSERT INTO, and means something like
"INSERT INTO unless a row with that primary key already exists, in which
case UPDATE". This is useful for implementing something with semantics
like Map.put, where a put can either create a new record or replace an
old one.
I thought this was actually standard, since SQL:2003 defines a MERGE
INTO command, but on looking more closely, i see that SQL:2003's version
is different, being more complicated and less useful. H2's version isn't
even a minimal subset of the SQL:2003 one, AFAICT. In H2, i say:
MERGE INTO table (key, value) VALUES (?, ?)
Whereas in SQL:2003 (as realised by Oracle, at least), i'd have to say:
MERGE INTO table USING table
ON (key = ?)
WHEN MATCHED THEN UPDATE SET value = ?
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (?, ?)
And i'd have to give my key and value twice in the parameters, unless i
wanted to use some kind of nonstandard (?) variable mechanism inside the
query. The SQL:2003 version is much more flexible, of course, and there
are all sorts of extra conditions you can add to it.
MySQL has yet another syntax for this, which looks like:
INSERT INTO table (key, value) VALUES (?, ?)
ON DUPLICATE KEY UPDATE value = VALUES(value)
As far as i can see, Derby has no way to do insert-or-update in a single
query.
Anyway, argh, looks like i'm going to have to add a facility for
database-specific SQL for the insert operation if i want this to be
portable. I'd really like to avoid having to fall back to running
multiple queries to probe the database for the existence of the key and
then do UPDATE or INSERT accordingly.