SQL standard UPSERT call

Question

I'm looking for a standard SQL "UPSERT" statement. A one call for insert and update if exists.

I'm looking for a working, efficient and cross platform call.

I've seen MERGE, UPSERT, REPLACE, INSERT .. ON DUPLICATE UPDATE but no statement meets the needs.

BTW I use MYSQL and HSQLDB for unitests. I understand that HSQLDB is limited and may not cover what I need, but I couldn't find a standard way even without it. A statement that only MYSQL and HSQLDB will also be enough for now.

I've been looking around for a while and couldn't get an answer.

My table:

CREATE TABLE MY_TABLE (
  MY_KEY varchar(50) NOT NULL ,
  MY_VALUE varchar(50) DEFAULT NULL,
  TIME_STAMP bigint NOT NULL,
  PRIMARY KEY (MY_KEY)
);

Any idea?

Answer 1

The only solution that is supported by both MySQL and HSQLDB is to query the rows you intend to replace, and conditionally either INSERT or UPDATE. This means you have to write more application code to compensate for the differences between RDBMS implementations.

1. START TRANSACTION.
2. SELECT ... FOR UPDATE.
3. If the SELECT finds rows, then UPDATE.
4. Else, INSERT.
5. COMMIT.

MySQL doesn't support the ANSI SQL MERGE statement. It supports REPLACE and INSERT...ON DUPLICATE KEY UPDATE. See my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" for more on that.

---

Re comments: Yes, another approach is to just try the INSERT and see if it succeeds. Otherwise, do an UPDATE. If you attempt the INSERT and it hits a duplicate key, it'll generate an error, which turns into an exception in some client interfaces. The disadvantage of doing this in MySQL is that it generates a new auto-increment ID even if the INSERT fails. So you end up with gaps. I know gaps in auto-increment sequence are not ordinarily something to worry about, but I helped a customer last year who had gaps of 1000-1500 in between successful inserts because of this effect, and the result was that they exhausted the range of an INT in their primary key.

As @baraky says, one could instead attempt the UPDATE first, and if that affects zero rows, then do the INSERT instead. My comment on this strategy is that UPDATEing zero rows is not an exception -- you'll have to check for "number of rows affected" after the UPDATE to know whether it "succeeded" or not.

But querying the number of rows affected returns you to the original problem: you have to use different queries in MySQL versus HSQLDB.

HSQLDB:

CALL DIAGNOSTICS(ROW_COUNT);

MySQL:

SELECT ROW_COUNT();