Geek asks and answers

SQL standard UPSERT call

I am looking for a standard SQL query UPSERT. One call to insert and update if exists.

I am looking for a working, efficient and cross-platform challenge.

I saw MERGE, UPSERT, REPLACE, INSERT .. ON DUPLICATE UPDATE, but no instruction does not meet the needs.

BTW I use MYSQL and HSQLDB for unitates. I understand that HSQLDB is limited and may not cover what I need, but I could not find a standard way even without it. The statement that at the moment only MYSQL and HSQLDB will suffice.

I looked around for a while and could not 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?

Thanx;)

+6
source share
2 answers

, MySQL HSQLDB, - , , INSERT, UPDATE. , , .

  • .
  • SELECT... .
  • SELECT , UPDATE.
  • Else, INSERT.
  • COMMIT.

MySQL ANSI SQL MERGE. REPLACE INSERT... ON DUPLICATE KEY UPDATE. . "INSERT IGNORE" vs "INSERT... ON DUPLICATE KEY UPDATE" .

Re comments: , - INSERT , . . INSERT, , , . MySQL , , INSERT . , . , , , 1000-1500 - , INT .

@baraky, UPDATE, , INSERT. , - " " ​​ , UPDATE , "" .

: MySQL HSQLDB.

HSQLDB:

CALL DIAGNOSTICS(ROW_COUNT);

MySQL:

SELECT ROW_COUNT();
+4

upsert RDBMS.

.

, . , .

+4

More articles:


All Articles