Question

teradata 数据库的merge 语句

teradata 数据库的 merge 语句怎么写？ 最好给一个例子！
谢谢！
必须是 teradata 能识别的语句！

Answer 1

需求：
将t2表的数据插入t1，当t2的a2值存在a1中时，使用t2的数据更新t1的数据，当a2的值不存在时，直接插入t1表
结果：可以使用Merge来实现此功能，测试如下：
CREATE TABLE dwsdata.t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER);
CREATE TABLE dwsdata.t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER);
select * from dwsdata.t1;
a1 b1 c1
1 1 1
2 2 2
3 3 3
select * from dwsdata.t2;
a2 b2 c2
1 2 2
2 3 3
4 5 5
MERGE INTO t1
USING t2
ON a1 = a2
WHEN MATCHED THEN
UPDATE SET b1 = b2,c1 = c2
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
预期结果：
1 2 2
2 3 3
3 3 3
4 5 5
select * from dwsdata.t1;
a1 b1 c1
1 2 2
2 3 3
3 3 3
4 5 5

Answer 2

MERGE INTO aa t1
USING bb t2
ON t1.RULE_ID = '2020080100111'
WHEN MATCHED THEN
UPDATE SET rule_exp = 'city_id in 400'
WHEN NOT MATCHED THEN
insert ('471','1','2020080100111', '111111');
*注意primary index
经常报错信息如果下：
The search condition must fully specify the target table primary index and partition column and expression must match insert specification primary index and partition column