An UPSERT statement works in the same manner as the combination of the INSERT and UPDATE statements. If the specified row already exists, the UPDATE statement is executed. If a row does not exist, the INSERT statement is executed. When you execute UPSERT statements, you must specify the columns of which the primary key consists. You can execute an UPSERT statement to write data in batches.

Syntax

UPSERT INTO table_name
              '('columns')' 
              VALUES '(' values ')'
columns ::=  column_name, columns
values  ::=  term, values,'(' values ')'
Note To write data in a batch, specify the columns to which you want to write data. You can specify the number of rows in the VALUES keyword.

Examples

  • Write data to specific columns for a row at a time.
    UPSERT INTO dt (p1,p2,c1,c2) VALUES(10, 20, 30, 40);
UPSERT INTO dt (p1,p2,c2) VALUES(10, 20, 40);   -- Writes data to only specific columns for a row.
UPSERT INTO dt (p1,p2,c1) VALUES(10, 20, 30);   -- Writes data to the other columns for the same row.
  • Write data to all specified columns at a time.
    UPSERT INTO dt (p1,p2,c1,c2) VALUES(1,2,3,4), (2,3,4,5), (3,4,5,6);