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
(我的方法)
- 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)
- Append set1 to table1
(将set1追加到table1)
insert into table1 select * from set1
- 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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…