Upsert statement
The word UPSERT combines UPDATE and INSERT, describing the statement's function.
Syntax:
upsert_statement ::=
[variable_declaration
]
UPSERT INTO table_name
[[AS] table_alias
]
["(" id
("," id)* ")"]
VALUES "(" insert_clause ("," insert_clause)* ")"
[SET TTL ttl_clause
]
[returning_clause]
insert_clause ::= DEFAULT | expression
returning_clause ::= RETURNING select_list
UPSERT
statement to insert a row where it does not exist, or
to update the row with new values when it does.
- Optional column(s) may be specified after the table name. This list contains the column names for a subset of the table’s columns. The subset must include all the primary key columns. If no columns list is present, the default columns list is the one containing all the columns of the table, in the order they are specified in the CREATE TABLE Statement.
- The columns in the columns list correspond one-to-one to the expressions (or DEFAULT keywords) listed after the VALUES clause (an error is raised if the number of expressions/DEFAULTs is not the same as the number of columns).
- Following the VALUES list a SET TTL clause may be used to set the expiration time of an upserted(inserted/updated) row.
- If there is no RETURNING clause, the result of the UPSERT statement is a record with a single field whose name is NumRowsInserted and whose value is the number of rows inserted: 0 if the row existed already and an update happened, or 1 otherwise. If there is a RETURNING clause, it acts the same way as the SELECT clause: it can be a *, in which case, a full row will be returned, or it can have a list of expressions specifying what needs to be returned.
users
table has three rows as shown
below:SELECT count(*) FROM users
{"Column_1":3}
Example 7-11 Update data in the users table using UPSERT command
The existing value for an user with id 10
is shown below.
SELECT * FROM users WHERE id=10
{
"id" : 10,
"firstName" : "John",
"lastName" : "Smith",
"otherNames" : [{
"first" : "Johny",
"last" : "BeGood"
}],
"age" : 22,
"income" : 45000,
"address" : {
"city" : "Reno",
"number" : 10,
"state" : "NV",
"street" : "Main"
},
"connections" : [30, 55, 43],
"expenses" : null
}
UPSERT
command. The otherNames
array and income
field is modified. UPSERT INTO users VALUES (
10,
"John",
"Smith",
[ {"first" : "Johny", "last" : "AlwaysGood"} ],
22,
80000,
{ "street" : "Main", "number" : 10, "city" : "Reno", "state" : "NV"},
[ 30, 55, 43 ],
DEFAULT
)
{"NumRowsInserted":0}
1 row returned
{"NumRowsInserted":0}
which implies that the row has been updated. The updated value for an user with id 10 can be verified with a SELECT statement as shown below.SELECT * FROM users WHERE id=10
{
"id" : 10,
"firstName" : "John",
"lastName" : "Smith",
"otherNames" : [{
"first" : "Johny",
"last" : "AlwaysGood"
}],
"age" : 22,
"income" : 80000,
"address" : {
"city" : "Reno",
"number" : 10,
"state" : "NV",
"street" : "Main"
},
"connections" : [30, 55, 43],
"expenses" : null
}
Example 7-12 Update only some columns using UPSERT statement
UPSERT INTO users VALUES ( 11, "John", "Smith" )
Error handling command UPSERT INTO users VALUES ( 11, "John", "Smith" ):
Error: at (1, 0) The number of VALUES expressions is not equal to the number of table columns
UPSERT INTO users(id,firstName,lastName) VALUES (11,"John","Smith")
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
which implies that one new row has been inserted. The updated value for an user with id 11 can be verified with a SELECT statement as shown below.SELECT * FROM users WHERE id=11
{
"id" : 11,
"firstName" : "John",
"lastName" : "Smith",
"otherNames" : null,
"age" : null,
"income" : null,
"address" : null,
"connections" : null,
"expenses" : null
}
UPSERT
statement has a NULL value.
Note:
Even if a column has a non NULL value ( for examplelastNames
in the query above), it can becomes NULL if it is omitted in a subsequent UPSERT
statement as shown below. Here you are using an optional RETURNING statement to fetch the data after the UPSERT is performed.UPSERT INTO users(id,firstName) VALUES (11,"Joseph") returning *
{
"id" : 11,
"firstName" : "Joseph",
"lastName" : null,
"otherNames" : null,
"age" : null,
"income" : null,
"address" : null,
"connections" : null,
"expenses" : null
}
The column lastNames
has become NULL because of the UPSERT
statement.
Example 7-13 Add a new shopper's record to the storeAcct
table.
You can use the UPSERT statement to add a new document or update fields in an existing document in a JSON collection table. Consider the JSON collection table created for a shopping application.
UPSERT into storeAcct values ("1417114588", {"firstName" : "Dori", "lastName" : "Martin", "email" : "dormartin@usmail.com", "address" : {"Dropbox" : "Presidency College"}}) RETURNING *;
Explanation: In the above example, you use the UPSERT statement to add a new row to the storeAcct
table.
You can use the UPSERT statement to update a shopper's information. Only the fields supplied in the UPSERT statement are updated in the document. The omitted fields are removed from the document.
{"contactPhone":"1417114588","address":{"Dropbox":"Presidency College"},"email":"lorphil@usmail.com","firstName":"Dori","lastName":"Martin"}