I was implementing upsert according to this great post: Insert, on duplicate update (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