UPSERT VERTEX¶
UPSERT VERTEX语句结合UPDATE和INSERT，如果点存在，会修改点的属性值；如果点不存在，会插入新的点。
Note
UPSERT VERTEX一次只能修改一个 Tag。
UPSERT VERTEX性能远低于INSERT，因为UPSERT是一组分片级别的读取、修改、写入操作。
Danger
禁止在高并发写操作的情况下使用UPSERT语句，请使用UPDATE或INSERT代替。
语法¶
ngql
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子句。
- 属性是否有默认值。
例如：
- 要插入的点包含基于 Tag
player的属性name和age。
SET子句指定age=30。
不同情况下的属性值如下表。
是否满足WHEN子句条件 |
属性是否有默认值 | name属性值 |
age属性值 |
|---|---|---|---|
| 是 | 是 | 默认值 | 30 |
| 是 | 否 | NULL |
30 |
| 否 | 是 | 默认值 | 30 |
| 否 | 否 | NULL |
30 |
示例如下：
```ngql // 查看三个点是否存在，结果 “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可以正常执行，例如：
```ngql 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子句的条件，就会修改点的属性值。
```ngql 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子句的条件，修改不会生效。
```ngql 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 | +---------------+-----+ ```