How do I use SQL statements to insert data - Lindorm - Alibaba Cloud Documentation Center

The UPSERT syntax works in the same manner as the combination of the INSERT and UPDATE syntaxes. If the specified row already exists, the UPDATE syntax is executed. If a row does not exist, the INSERT syntax is executed. When you execute an UPSERT statement, you must specify the column that is used as the primary key. The UPSERT syntax can be used to insert individual rows or multiple rows of data with timestamps in a batch. In this case, the UPSERT syntax is equivalent to the INSERT syntax.

Applicable engines and versions

The UPSERT syntax is applicable to all versions of LindormTable and LindormTSDB.

Comparison between the INSERT operation in rational database and the UPSERT operation in LindormTable

The UPSERT operation in LindormTable is different from the INSERT operation in rational databases.

INSERT operations in traditional rational databases
For a table with a specified primary key, if you perform two consecutive INSERT operations without specifying the ON DUPLICATE KEY extension to write data with the same primary key value, the second INSERT operation fails. In this case, you must explicitly execute the UPDATE statement to update data or specify the ON DUPLICATE KEY extension in the second INSERT operation.
Lindorm
- LindormTable: If you perform two consecutive UPSERT operations to write data with the same primary key, the second UPSERT operation overwrites the row of data that is written by the first UPSERT operation without errors reported. In this case, LindormTable stores the rows written by the two operation as different versions. By default, when a SELECT statement is executed to query the row of data, the latest version of each column in the row is returned.
- LindormTSDB: If you perform two consecutive UPSERT operations to write data with the same primary key, the second UPSERT operation overwrites the row of data that is written by the first UPSERT operation.

For the example on how to use the UPSERT syntax to insert rows with the same primary key, see Write rows with the same primary key.

Syntax

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

Parameters

HINT expression (hint_expression)

HINT expressions are supported only by LindormTable.

You can use the _l_ts_ hint in an UPSERT statement to specify a timestamp for the row that you want to update or insert. For more information about the _l_ts_ hint, see Parameters of hintOptions.

ON DUPLICATE KEY

The ON DUPLICATE KEY clause is supported only by LindormTable.

The ON DUPLICATE KEY clause is used to check whether the specified row exists and is similar to the CHECK AND PUT operation in HBase. The clause complies to the following rules:

You can use the ON DUPLICATE KEY clause followed by the UPDATE keyword to update the values in the specified column. If the specified column already exists, the UPDATE statement is executed to update the values in the column. If the specified column does not exist, no data is updated and no error is reported. For example, if you execute the ON DUPLICATE KEY UPDATE c1 = 20 statement and the c1 column exists, the value in the c1 column is updated to 20. If the c1 column does not exist, no data is updated and no error is reported.
You can also use the IGNORE keyword after the ON DUPLICATE KEY clause. In this case, if the specified column already exists, no data is updated and no error is reported. If the specified column does not exist, data in the specified column is updated.
The ON DUPLICATE KEY clause is applicable only to tables for which the CONSISTENCY option is set to strong. For more information about the CONSISTENCY option, see CREATE TABLE. To change the value of CONSISTENCY for a table, see ALTER TABLE.

Examples

In the following examples, the sample table sensor is created by executing the following statement:

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);