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?

`INSERT .. ON DUPLICATE UPDATE` is the key in `mYSQL`. can you show some records or maybe the table structure? — John Woo, Mar 06 '13 at 16:04
Yeah but it is supported in MYSQL only. HSQLDB and all others don't use it... — BobTheBuilder, Mar 06 '13 at 16:05
there's an ANSI merge (http://en.wikipedia.org/wiki/Merge_%28SQL%29), but it's not implemented in all DBMS. So no hope for an universal command. — Raphaël Althaus, Mar 06 '13 at 16:07
just in case you can use ruby, my (warning self-promotion) [Upsert](https://github.com/seamusabshere/upsert) library will automatically define an upsert function in mysql and postgres that can be called as a single command. extensive tests included. — Seamus Abshere, Aug 16 '13 at 16:28
also check out [the canonical postgres upsert function example](http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING) and @DennisSHennen's [translation into mysql](http://stackoverflow.com/questions/11371479/how-to-translate-postgresql-merge-db-aka-upsert-function-into-mysql/) — Seamus Abshere, Aug 16 '13 at 16:29

score 6 · Answer 1 · edited Dec 11 '18 at 23:37

6

The syntax for doing an upsert in a single command varies by RDBMS.

See Wikipedia for more.

If you want a cross platform solution, then you'll need to use multiple commands. First check for the existing row, then conditionally insert or update as appropriate.

edited Dec 11 '18 at 23:37

Basil Bourque

303,325
100
852
1,154

answered Mar 06 '13 at 16:10

Ike Walker

64,401
14
110
109

I don't want to use multiple commands. Any other ideas how test and dev will run the same code? – BobTheBuilder Mar 06 '13 at 16:15
I still recommend doing it using conditional code. If you won't do that, then consider standardizing your test and dev environments to use the same RDBMS. – Ike Walker Mar 06 '13 at 17:07
1

@baraky: if you want to support multiple DBMS you will never be able to solve every problem with a statement that runs on all DBMS. Be prepared to have DBMS specific statement. Everything else is going to fail. – Mar 06 '13 at 17:31

score 6 · Accepted Answer · edited May 23 '17 at 12:02

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.

START TRANSACTION.
SELECT ... FOR UPDATE.
If the SELECT finds rows, then UPDATE.
Else, INSERT.
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();

Or perform INSERT, catch SQLException and if it is an integrity constraint violation, perform an UPDATE — fredt, Mar 06 '13 at 18:31
Actually that's (almost) what I'm going to do - I'm gonna update, catch the exception (as updates are the common case for me) and insert if fails. Thanx! — BobTheBuilder, Mar 07 '13 at 07:02

SQL standard UPSERT call

2 Answers2

Linked