英文:
Can this SQL MERGE table copy be written as a single operation?
问题
使用DB2 for i平台，我已成功使用两个操作来从一个表中执行整个表数据更新到另一个表中：
-- 将源文件中的值放入目标文件
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);
-- 删除在目标文件中但不在源文件中的记录
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);
这已经给我想要的结果，但我不能不去思考是否有一种方法将其合并为一个语句，也许在合并语句内部对源表进行某种连接操作。
英文:
Using the DB2 for i platform, I have been successfully using two operations to do a whole-table data update from one table to another:
-- 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);
This has been giving me the result I want, but I can't help think that there is a way to make this into one statement, maybe some kind of join on the source table inside of the merge statement.
答案1
得分: 0
不需要翻译的代码部分：
MERGE INTO TLIB.TABLE AS T USING (
select 'SLIB' PROVENANCE, sl.* from SLIB.TABLE sl
union all select 'TLIB' PROVENANCE, tl.* from TLIB.TABLE TL left join SLIB.TABLE SL on (sl.key1, sl.key2) = (tl.key1, tl.key2) where sl.key1 is null
) 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 and PROVENANCE = 'SLIB' THEN
UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED and PROVENANCE = 'TLIB' THEN
DELETE;
英文:
You can build a union of rows from SLIB and rows from TLIB that are not in SLIB with a provenance column and test that provenance column when matched
MERGE INTO TLIB.TABLE AS T USING (
select 'SLIB' PROVENANCE, sl.* from SLIB.TABLE sl
union all select 'TLIB' PROVENANCE, tl.* from TLIB.TABLE TL left join SLIB.TABLE SL on (sl.key1, sl.key2) = (tl.key1, tl.key2) where sl.key1 is null
) 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 and PROVENANCE = 'SLIB' THEN
UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED and PROVENANCE = 'TLIB' THEN
DELETE;
答案2
得分: 0
如果在合并之前对源表和目标表执行完全联接，然后可以确定哪些记录存在于其中一个表中、另一个表中，或两者都存在，并相应调整合并操作：
MERGE INTO TLIB.TABLE AS T USING (
SELECT S1.*, T1.KEY1 AS TKEY1, T1.KEY2 AS TKEY2
FROM SLIB.TABLE AS S1
FULL OUTER JOIN TLIB.TABLE AS T1
ON T1.KEY1 = S1.KEY1 AND T1.KEY2 = S1.KEY2
) AS S ON COALESCE(S.KEY1, TKEY1) = T.KEY1 AND
COALESCE(S.KEY2, TKEY2) = T.KEY1
WHEN NOT MATCHED THEN
INSERT VALUES(S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED AND S.KEY1 IS NULL THEN DELETE
WHEN MATCHED AND S.KEY1 IS NOT NULL THEN
UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1);
英文:
If you do a full join of the source and target tables before you merge, you can then tell which records exist in one, the other, or both and adjust your merge actions accordingly:
MERGE INTO TLIB.TABLE AS T USING (
SELECT S1.*, T1.KEY1 AS TKEY1, T1.KEY2 AS TKEY2
FROM SLIB.TABLE AS S1
FULL OUTER JOIN TLIB.TABLE AS T1
ON T1.KEY1 = S1.KEY1 AND T1.KEY2 = S1.KEY2
) AS S ON COALESCE(S.KEY1, TKEY1) = T.KEY1 AND
COALESCE(S.KEY2, TKEY2) = T.KEY1
WHEN NOT MATCHED THEN
INSERT VALUES(S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED AND S.KEY1 IS NULL THEN DELETE
WHEN MATCHED AND S.KEY1 IS NOT NULL THEN
UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论