1

I was implementing upsert according to this great post: Insert, on duplicate update in PostgreSQL? and this works really fine with psql. However, I can't fire the same query into hsqldb which i use for testing. my sql looks like this:

UPDATE account set name = ?, email = ?, type = ?, regts = ?, dao_updated = ? WHERE id = ?; 
INSERT INTO account (id, name, email, type, regts, dao_created,dao_updated) SELECT ?,?,?,?,?,?,? WHERE NOT EXISTS (SELECT 1 FROM account WHERE id = ? );

I get the error message:

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: INSERT
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)

Is this a limitation of hsqldb or am I doing something wrong?

thanks in advance Leon

Community
  • 1
  • 1
Leon
  • 1,141
  • 1
  • 10
  • 25

2 Answers2

0

You apparently can't use several statements inside single executeUpdate() call. Please, call twice first time doing UPDATE, next time doing INSERT

Archer
  • 5,073
  • 8
  • 50
  • 96
0

HSQLDB supports the MERGE statement for this:

http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_merge_statement

The merge statement is generally more powerful than UPSERT and non-standard alternatives.

Alternatively, you can use CREATE PROCEDURE and define a procedure similar to the PostgreSQL example given in the linked answer.

http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_conditional

You can use the GET DIAGNOSTICS statement to find out if the update succeeded.

http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_diagnostics_state

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks, but the idea was to use hsqldb for junit tests and psql for production, and this doesn't work with two dialects ;-) – Leon Jan 09 '13 at 09:27