UPSERT VERTEX

UPSERT VERTEX语句结合UPDATE和INSERT，如果点存在，会修改点的属性值；如果点不存在，会插入新的点。

Note

UPSERT VERTEX一次只能修改一个 Tag。

UPSERT VERTEX性能远低于INSERT，因为UPSERT是一组分片级别的读取、修改、写入操作。

Danger

禁止在高并发写操作的情况下使用UPSERT语句，请使用UPDATE或INSERT代替。

语法

UPSERT VERTEX ON <tag> <vid>
SET <update_prop>
[WHEN <condition>]
[YIELD <output>]

参数	是否必须	说明	示例
ON <tag>	是	指定点的 Tag。要修改的属性必须在这个 Tag 内。	ON player
<vid>	是	指定要修改或插入的点 ID。	"player100"
SET <update_prop>	是	指定如何修改属性值。	SET age = age +1
WHEN <condition>	否	指定过滤条件。	WHEN name == "Tim"
YIELD <output>	否	指定语句的输出格式。	YIELD name AS Name

插入不存在的点

如果点不存在，无论WHEN子句的条件是否满足，都会插入点，同时执行SET子句，因此新插入的点的属性值取决于：

• SET子句。

• 属性是否有默认值。

例如：

• 要插入的点包含基于 Tagplayer的属性name和age。

• SET子句指定age=30。

不同情况下的属性值如下表。

是否满足WHEN子句条件	属性是否有默认值	name属性值	age属性值
是	是	默认值	30
是	否	NULL	30
否	是	默认值	30
否	否	NULL	30

示例如下：

// 查看三个点是否存在，结果 “Empty set” 表示顶点不存在。
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__ |
+---------+----------+

上面最后一个示例中，因为age没有默认值，插入点时，age默认值为NULL，执行age = age + 1后仍为NULL。如果age有默认值，则age = age + 1可以正常执行，例如：

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  |
+----------+-----+

修改存在的点

如果点存在，且满足WHEN子句的条件，就会修改点的属性值。

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  |
+---------------+-----+

如果点存在，但是不满足WHEN子句的条件，修改不会生效。

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  |
+---------------+-----+

---

最后更新: June 20, 2023