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";
+-----------+
| vertices_ |
+-----------+
+-----------+
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";
+-----------------------------------------------------+
| vertices_                                           |
+-----------------------------------------------------+
| ("player101" :player{age: 42, 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" | 44  |
+---------------+-----+

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

nebula> FETCH PROP ON player "player101";
+-----------------------------------------------------+
| vertices_                                           |
+-----------------------------------------------------+
| ("player101" :player{age: 44, 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" | 44  |
+---------------+-----+

Last update: March 13, 2023