UPSERT-Lindorm(Lindorm)-阿里云帮助中心

UPSERT inserts a row if it does not exist, or updates it if it does. When you execute UPSERT statements, you must include all columns that make up the primary key.

Note Every UPSERT statement must include all columns that make up the primary key.

Syntax

UPSERT INTO table_name (columns) VALUES (values)

columns ::= column_name [, column_name ...]
values  ::= term [, term ...] [, (term [, term ...]) ...]

Examples

Write to a new row

UPSERT INTO dt (p1, p2, c1, c2) VALUES (10, 20, 30, 40);

Write to a subset of columns

The following two statements each write to a different non-primary-key column for the same row (identified by p1=10, p2=20):

UPSERT INTO dt (p1, p2, c2) VALUES (10, 20, 40);  -- writes only to c2
UPSERT INTO dt (p1, p2, c1) VALUES (10, 20, 30);  -- writes only to c1

Write multiple rows in one statement

Specify multiple value tuples in the VALUES clause to write several rows in a single statement:

UPSERT INTO dt (p1, p2, c1, c2) VALUES (1, 2, 3, 4), (2, 3, 4, 5), (3, 4, 5, 6);