3

I know that MySQL supports this query (ON DUPLICATE KEY UPDATE) for example:

INSERT INTO UserFailure (Realm, Username, DateOfFailure, CountOfFailure) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE CountOfFailure=?;

When I am running this on embedded Derby DB it gives me:

PreparedStatementCallback; bad SQL grammar [INSERT INTO UserFailure (Realm, Username, DateOfFailure, CountOfFailure) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE CountOfFailure=?;]; nested exception is java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ON" at line 1, column 91.

I assume that Derby does not support this feature.

Is there a query I can use to update the database record on duplicate key from Derby and from MySQL?

Ross
  • 1,313
  • 4
  • 16
  • 24
Paulius Matulionis
  • 23,085
  • 22
  • 103
  • 143

1 Answers1

2

You can catch the duplicate key exception yourself, and issue the update.

Bryan Pendleton
  • 16,128
  • 3
  • 32
  • 56
  • Yes I know I can do that, but aren't there any query that will do the same in Derby and MySql? – Paulius Matulionis Sep 11 '12 at 07:51
  • MySQL's SQL language extensions are generally not implemented by other databases. See this related question for some more ideas: http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update – Bryan Pendleton Sep 11 '12 at 13:34