Kudu条件升级INTO

Question

酷都支持UPSERT INTO的UPDATE部分的条件吗？

我可以提供一个条件子句，根据插入值和目标表之间的比较只更新给定的值吗？实际的用例是用最新的更新时间戳列。

这是我想象中的行为。

CREATE TABLE my_first_table
(
id INT,
name STRING,
status INT,
PRIMARY KEY(id)
)
PARTITION BY HASH PARTITIONS 4
STORED AS KUDU;
INSERT INTO my_first_table VALUES (1, "lee", 101), (2 "shiv", 102), (3,"bob", 103);
--CONDITION FALSE, UPDATE NOT PERFORMED
UPSERT INTO my_first_table AS t 
VALUES (3, "bobby", 100) AS v 
WHERE v.status > t.status
+----+------+--------+
| id | name | status |
+----+------+--------+
| 1  | lee  | 101    |
| 2  | shiv | 102    |
| 3  | bob  | 103    |
+----+------+--------+
--CONDITION TRUE, UPDATE PERFORMED
UPSERT INTO my_first_table AS t 
VALUES (3, "bobby", 100) AS v 
WHERE v.status < t.status
+----+------+--------+
| id | name | status |
+----+------+--------+
| 1  | lee  | 101    |
| 2  | shiv | 102    |
| 3  | bobby| 100    |
+----+------+--------+

如果不存在3，则应插入。

如果没有，有没有一个优雅的解决方法？

Answer 1

我找到的一个解决方案是在SELECT表达式中使用LEFT JOIN和筛选器。假设我们有一个与目标表相同的表to_upsert，其中包含所有潜在的追加。。。

INSERT INTO to_upsert VALUES (3, "bobby" 100), (5, "newgal", 600);
UPSERT INTO my_first_table
SELECT to_upsert.id, to_upsert.name, to_upsert.status
FROM to_upsert
LEFT JOIN my_first_table ON to_upsert.id = my_first_table.id
WHERE my_first_table.status > to_upsert.status OR my_first_table.id IS NULL;
SELECT * FROM my_first_table;
+----+--------+--------+
| id | name   | status |
+----+--------+--------+
| 3  | bobby  | 100    |
| 1  | lee    | 101    |
| 2  | shiv   | 102    |
| 5  | newgal | 600    |
+----+--------+--------+

感谢您观看本期我学习sql的节目。