Use UPSERT Syntax to Handle Conflicts & Simplify Writes - Lindorm

UPSERT inserts a row if the primary key does not exist, or updates it if it does. You must specify the primary key columns in every UPSERT statement. UPSERT is supported by both LindormTable and LindormTSDB across all versions.

Unlike in relational databases, UPSERT in Lindorm never fails on a duplicate primary key — the row is created or updated silently.

Choose your write behavior

Use the table below to pick the right clause for your use case:

Goal	Syntax
Insert or overwrite (default)	`UPSERT INTO ... VALUES ...`
Insert only; skip if the row exists	`... ON DUPLICATE KEY IGNORE`
Update specific columns if the row exists; insert if it does not	`... ON DUPLICATE KEY UPDATE col = val`
Insert only; error if the row exists	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8+)

Note

ON DUPLICATE KEY clauses are supported only by LindormTable, and only on tables where the CONSISTENCY parameter is set to strong.

How LindormTable and LindormTSDB differ

When you write two rows with the same primary key:

LindormTable — the second write overwrites the row written by the first without errors. LindormTable stores both writes as different versions. A standard SELECT returns the latest version of each column. Use the _l_versions_ hint to retrieve all versions.
LindormTSDB — the second write overwrites the first. No versioning is retained.

Syntax

upsert_statement   ::= { UPSERT | INSERT } [ hint_expression ]
                       INTO table_identifier columns_declaration
                       VALUES value_list ( ',' value_list)*
                       [ ON DUPLICATE KEY column_identifier =
                         value_literal | IGNORE ]
columns_declaration ::=  '(' column_identifier ( ',' column_identifier)* ')'
value_list          ::=  '(' value_expression( ',' value_expression)* ')'

Parameters

Hint expression

LindormTable only. Use the _l_ts_ hint to set an explicit timestamp for the row being written:

UPSERT /*+ _l_ts_(111232) */ INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:00', 12.1);

For all available hint options, see Parameters of hintOptions.

ON DUPLICATE KEY

LindormTable only. Checks whether the specified row exists before writing — similar to the checkAndPut operation in HBase.

Important

In Lindorm SQL 2.8.8.2 and later, use NOW() in a VALUES clause to insert the current timestamp automatically. Example: UPSERT INTO tb (id, ts) VALUES (1, NOW());. To check your Lindorm SQL version, see SQL versions.

ON DUPLICATE KEY IGNORE

If the row exists, the write is skipped without error. If the row does not exist, data is inserted.

UPSERT INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 13.2)
ON DUPLICATE KEY IGNORE;

ON DUPLICATE KEY UPDATE

If the row exists, the specified column is updated to the given value. Behavior when the row does not exist depends on the LindormTable version:

Version	Row does not exist
Earlier than 2.7.8	No update; no error
2.7.8 and later	An error is reported and data in the `VALUES` clause is inserted

UPSERT INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 13.2)
ON DUPLICATE KEY UPDATE temperature = 30;

ON DUPLICATE KEY ERROR

LindormTable 2.7.8 and later only. If the row exists, an error is reported. If the row does not exist, data is inserted.

UPSERT INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 13.2)
ON DUPLICATE KEY ERROR;

Examples

All examples use the following sample table:

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region    VARCHAR NOT NULL,
    time      TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity    BIGINT,
    PRIMARY KEY (device_id, region, time)
) WITH (VERSIONS=2);

Write a single row

UPSERT INTO sensor (device_id, region, time, temperature, humidity)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:00', 12.1, 45);

Verify: SELECT * FROM sensor;

Write to specific columns

UPSERT INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 13.2);

Verify: SELECT * FROM sensor;

Write multiple rows in one statement

Separate each row with a comma in the VALUES clause.

UPSERT INTO sensor (device_id, region, time, temperature)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:20', 10.6),
       ('F07A1261', 'south-cn', '2021-04-22 15:33:00', 18.1),
       ('F07A1261', 'south-cn', '2021-04-22 15:33:10', 19.7);

Verify: SELECT * FROM sensor;

Write rows with the same primary key (LindormTable versioning)

LindormTable stores each write as a new version instead of overwriting in place. The following steps show how this works.

Note

In LindormTSDB, the second write overwrites the first and no versioning is retained.

Write the first row.

UPSERT INTO sensor (device_id, region, time, temperature, humidity)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 13.2, 45);

Query the data.

SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn';

Output:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
+-----------+----------+-------------------------------+-------------+----------+

Write the row a second time with different values but the same primary key.

UPSERT INTO sensor (device_id, region, time, temperature, humidity)
VALUES ('F07A1260', 'north-cn', '2021-04-22 15:33:10', 16.7, 52);

Query again. The latest version is returned by default.

SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn';

Output:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
+-----------+----------+-------------------------------+-------------+----------+

Use the _l_versions_ hint to retrieve all stored versions.

SELECT /*+ _l_versions_(2) */ device_id, region, time, temperature, humidity
FROM sensor
WHERE device_id='F07A1260';

Output:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
+-----------+----------+-------------------------------+-------------+----------+

Both writes are preserved as separate versions.

What's next

ALTER TABLE — change the CONSISTENCY parameter on an existing table to enable ON DUPLICATE KEY clauses
Table attributes (table_options) — set CONSISTENCY=strong at table creation time
Parameters of hintOptions — full reference for _l_ts_ and other hints
SQL versions — check your Lindorm SQL version