Skip to content

UPSERT VERTEX

The UPSERT statement is a combination of UPDATE and INSERT. You can use UPSERT VERTEX to update the properties of a vertex if it exists or insert a new vertex if it does not exist.

Note

An UPSERT VERTEX statement can only update the properties on ONE TAG of a vertex.

The performance of UPSERT is much lower than that of INSERT because UPSERT is a read-modify-write serialization operation at the partition level.

Danger

Don't use UPSERT for scenarios with highly concurrent writes. You can use UPDATE or INSERT instead.

Syntax

UPSERT VERTEX ON <tag> <vid>
SET <update_prop>
[WHEN <condition>]
[YIELD <output>]
Parameter Required Description Example
ON <tag> Yes Specifies the tag of the vertex. The properties to be updated must be on this tag. ON player
<vid> Yes Specifies the ID of the vertex to be updated or inserted. "player100"
SET <update_prop> Yes Specifies the properties to be updated and how they will be updated. SET age = age +1
WHEN <condition> No Specifies the filter conditions. WHEN name == "Tim"
YIELD <output> No Specifies the output format of the statement. YIELD name AS Name

Insert a vertex if it does not exist

If a vertex does not exist, it is created no matter the conditions in the WHEN clause are met or not, and the SET clause always takes effect. The property values of the new vertex depend on:

  • How the SET clause is defined.
  • Whether the property has a default value.

For example, if:

  • The vertex to be inserted will have properties name and age based on the tag player.
  • The SET clause specifies that age = 30.

Then the property values in different cases are listed as follows:

Are WHEN conditions met If properties have default values Value of name Value of age
Yes Yes The default value 30
Yes No NULL 30
No Yes The default value 30
No No NULL 30

Here are some examples:

// This query checks if the following three vertices exist. The result "Empty set" indicates that the vertices do not exist.
nebula> FETCH PROP ON * "player666", "player667", "player668" YIELD properties(vertex);
+--------------------+
| properties(VERTEX) |
+--------------------+
+--------------------+
Empty set

nebula> UPSERT VERTEX ON player "player666" \
        SET age = 30 \
        WHEN name == "Joe" \
        YIELD name AS Name, age AS Age;
+----------+----------+
| Name     | Age      |
+----------+----------+
| __NULL__ | 30       |
+----------+----------+

nebula> UPSERT VERTEX ON player "player666" \
        SET age = 31 \
        WHEN name == "Joe" \
        YIELD name AS Name, age AS Age;
+----------+-----+
| Name     | Age |
+----------+-----+
| __NULL__ | 30  |
+----------+-----+

nebula> UPSERT VERTEX ON player "player667" \
        SET age = 31 \
        YIELD name AS Name, age AS Age;
+----------+-----+
| Name     | Age |
+----------+-----+
| __NULL__ | 31  |
+----------+-----+

nebula> UPSERT VERTEX ON player "player668" \
        SET name = "Amber", age = age + 1 \
        YIELD name AS Name, age AS Age;
+---------+----------+
| Name    | Age      |
+---------+----------+
| "Amber" | __NULL__ |
+---------+----------+

In the last query of the preceding examples, since age has no default value, when the vertex is created, age is NULL, and age = age + 1 does not take effect. But if age has a default value, age = age + 1 will take effect. For example:

nebula> CREATE TAG IF NOT EXISTS player_with_default(name string, age int DEFAULT 20);
Execution succeeded

nebula> UPSERT VERTEX ON player_with_default "player101" \
        SET age = age + 1 \
        YIELD name AS Name, age AS Age;

+----------+-----+
| Name     | Age |
+----------+-----+
| __NULL__ | 21  |
+----------+-----+

Update a vertex if it exists

If the vertex exists and the WHEN conditions are met, the vertex is updated.

nebula> FETCH PROP ON player "player101" YIELD properties(vertex);
+--------------------------------+
| properties(VERTEX)             |
+--------------------------------+
| {age: 36, name: "Tony Parker"} |
+--------------------------------+

nebula> UPSERT VERTEX ON player "player101" \
        SET age = age + 2 \
        WHEN name == "Tony Parker" \
        YIELD name AS Name, age AS Age;
+---------------+-----+
| Name          | Age |
+---------------+-----+
| "Tony Parker" | 38  |
+---------------+-----+

If the vertex exists and the WHEN conditions are not met, the update does not take effect.

nebula> FETCH PROP ON player "player101" YIELD properties(vertex);
+--------------------------------+
| properties(VERTEX)             |
+--------------------------------+
| {age: 38, name: "Tony Parker"} |
+--------------------------------+

nebula> UPSERT VERTEX ON player "player101" \
        SET age = age + 2 \
        WHEN name == "Someone else" \
        YIELD name AS Name, age AS Age;
+---------------+-----+
| Name          | Age |
+---------------+-----+
| "Tony Parker" | 38  |
+---------------+-----+
Last update: February 8, 2022
Back to top