sql - ＃在Teradata中更新并追加。 / *从表2更新表1中的现有记录。如果表2具有新记录，则在表1中追加新记录* /(#Update and Append in Teradata. /* Update existing records in table 1 from table 2. Append new records in table 1 if table 2 have new record */)

Question

I have table1 as

(我有table1作为)

I have table2 as

(我有table2作为)

I need to update table1 with table2 values.

(我需要用table2值更新table1。)

same mbr_id records in both table should be replaced by table2 value.

(两个表中相同的mbr_id记录应替换为table2值。)

new mbr_id in table2 should append to table1.

(表2中的新mbr_id应该附加到表1中。)

Expected output:

(预期产量：)

My approach

(我的方法)

1. separate existing mbr_id and new mbr_id from table2

   (从table2分离现有的mbr_id和新的mbr_id)

create table set1 as select * from table2 where mbr_id not in (select mbr_id from table1)
create table set2 as select *  from table2 where mbr_id in (select mbr_id from table1)

2. Append set1 to table1

   (将set1追加到table1)

insert into table1 select * from set1

3. update table1 with set2

   (用set2更新table1)

UPDATE t1
FROM table1  t1,
set2 t2
SET 
sales_bucket = t2.sales_bucket,
pro = t2.pro,
Region = t2.Region
where t1.mbr_id = t2.mbr_id

The question is: Is there any better way to perform the same in a single go ?

(问题是：有没有更好的方法可以一次执行相同的操作？)

  ask by Anil Kumar translate from so

Answer 1

The MERGE statement combines the UPDATE and INSERT statements into a single statement with two conditional test clauses: WHEN MATCHED, UPDATE.

(MERGE语句将UPDATE和INSERT语句组合为带有两个条件测试子句的单个语句：WHAT MATCHED，UPDATE。)

WHEN NOT MATCHED, INSERT.

(未匹配时，插入。)

You can also use the MERGE statement to delete rows by specifying: WHEN MATCHED, DELETE.

(您还可以使用MERGE语句通过指定以下内容来删除行：WHEN MATCHED，DELETE。)