SQL insert on conflict

|
Programming/ SQL
| Programming SQL

SQL insert on conflict

PostgreSQL

1
2
3
4
5
6
7
INSERT INTO MYBAAS(ID, TYPE_NAME, JSON)
VALUES(?, ?, ?)
ON CONFLICT(ID) DO UPDATE SET TYPE_NAME=?, JSON=?

INSERT INTO MYBAAS(ID, TYPE_NAME, JSON)
VALUES(?, ?, ?)
ON CONFLICT DO NOTHING;

SQL Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE MYBAAS (
        ID          VARCHAR(64) NOT NULL,
        TYPE_NAME   VARCHAR(64) NOT NULL,
        JSON        LONGTEXT NOT NULL,
        CONSTRAINT MYBAAS_PK PRIMARY KEY (ID) WITH (IGNORE_DUP_KEY=ON)
);

CREATE TABLE IF NOT EXISTS PROPERTY_MAP (
        ID              VARCHAR(64) NOT NULL,
        PROPERTY_NAME   VARCHAR(64) NOT NULL,
        COLUMN_NAME     VARCHAR(64) NOT NULL,
        TYPE_NAME       VARCHAR(64) NOT NULL,
        CONSTRAINT PROPERTY_MAP_PK PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX PROPERTY_MAP_UK01
ON PROPERTY_MAP(PROPERTY_NAME) WITH (IGNORE_DUP_KEY = ON);

CREATE UNIQUE INDEX PROPERTY_MAP_UK02
ON PROPERTY_MAP(COLUMN_NAME) WITH (IGNORE_DUP_KEY = ON);

SQLite

1
2
INSERT OR REPLACE INTO MYBAAS(ID, TYPE_NAME, JSON)
VALUES(?, ?, ?);

MariaDB/MySQL

1
2
3
INSERT INTO MYBAAS(ID, TYPE_NAME, JSON)
VALUES(?, ?, ?)
ON DUPLICATE KEY UPDATE TYPE_NAME=?, JSON=?

Oracle

1
2
3
4
5
6
7
8
MERGE INTO MYBAAS D
    USING (SELECT ? AS ID, ? AS TYPE_NAME, ? AS JSON FROM DUAL) S
    ON (D.ID = S.ID)
    WHEN MATCHED THEN
        UPDATE SET D.TYPE_NAME = S.TYPE_NAME
    WHEN NOT MATCHED THEN
        INSERT (D.ID, D.TYPE_NAME, D.JSON)
        VALUES (S.ID, S.TYPE_NAME, S.JSON);

DB2

1
2
3
4
5
6
7
MERGE INTO MYBAAS AS D
    USING (VALUES(?, ?, ?)) AS S(ID, TYPE_NAME, JSON)
    ON D.ID = S.ID
    WHEN MATCHED THEN
        UPDATE SET D.TYPE_NAME = S.TYPE_NAME, D.JSON = S.JSON
    WHEN NOT MATCHED THEN
        INSERT (ID, TYPE_NAME, JSON) VALUES (S.ID, S.TYPE_NAME, S.JSON);