在使用DB2 for i平台时,我已经成功地使用了两个操作来完成从一个表到另一个表的全表数据更新:
-- put values from source file into target file
MERGE INTO TLIB.TABLE AS T USING SLIB.TABLE AS S
ON (T.KEY1 = S.KEY1 AND T.KEY2 = S.KEY2)
WHEN NOT MATCHED THEN
INSERT VALUES(S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED THEN
UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1);
-- remove records that are in target file but not source file
DELETE FROM TLIB.TABLE T2
WHERE RRN(T2) IN (
SELECT RRN(T)
FROM TLIB.TABLE AS T
LEFT JOIN SLIB.TABLE AS S
ON (T.KEY1 = S.KEY1 AND T.KEY2 = S.KEY2)
WHERE S.KEY1 IS NULL);
这已经给了我想要的结果,但是我不禁想到有一种方法可以把它变成一个语句,也许是在merge语句内部对源表进行某种连接。
1条答案
按热度按时间xiozqbni1#
您可以构建
SLIB
中的行与TLIB that are not in SLIB
中的行的联合,并使用一个来源列,然后在匹配时测试该来源列