合并更新时间 [英] Merge update Temporal
问题描述
表测试中的现有数据:(临时表)
Existing data in table test : (Temporal table)
id name valid_dt
1 cat 2012-06-16 - 9999-12-31
传入表:(临时)
id name valid_dt
1 bat 2013-12-28 - 9999-12-31
合并更新后,测试表应具有
After Merge update , test table should have
id name valid_dt
1 cat 2012-06-16 - 2013-12-28
1 bat 2013-12-28 - 9999-12-31
如果我今天运行它,Teradata临时合并更新语句是否可能?
Will It be possible in Teradata Temporal Merge update statement if i run it today?
我尝试过的无效代码
SEQUENCED VALIDTIME
MERGE INTO test
USING
(
sel * from incoming
) H on id=H.id
when matched then
update
set name = h.name
;
更新尝试dnoeths解决方案后: 要删除:
UPDATE After trying dnoeths solution: For delete :
在我的实际传入表中,实际上已关闭了一些记录.
In my real incoming table some records were actually closed .
所以我需要硬代码的结束日期,直到until_changed
so i need hard code end date as until_changed
无效删除:
sequenced validtime
delete from test where (cust_id,name) in
(nonsequenced validtime
select id,name,period(begin(valid_dt),until_changed)
from incoming );
推荐答案
AFAIK,您需要执行两步过程来维护这样的ValidTime表:先是先后执行先后顺序的ValidTime DELETE和未顺序的ValidTime INSERT:
AFAIK you need to a two step process to maintain a ValidTime table like this, a Sequenced ValidTime DELETE followed by a NonSequenced ValidTime INSERT:
SEQUENCED VALIDTIME
DELETE test FROM incoming
WHERE test.id = incoming.id
;NONSEQUENCED VALIDTIME
INSERT INTO test SELECT * FROM incoming;
应该可以用合并替换插入/选择,但是您仍然需要先删除:
Replacing the Insert/Select with Merge should be possible, but you still need the Delete first:
NONSEQUENCED VALIDTIME
MERGE INTO test
USING
(
sel * from incoming
) H ON test.id=H.id
AND test.valid_dt = h.valid_dt
WHEN NOT MATCHED THEN
INSERT (H.id, H.name, h.valid_dt)
像非时间DML一样,MERGE可能比插入/选择或更新更有效.
Like non-temporal DML the MERGE might be more efficient than Insert/Select or Update.
这篇关于合并更新时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!