最终桌的合并金额是多少? [英] What is the Final Table equivalent for Merge?
问题描述
我正在尝试使用merge into插入新记录。我想收集插入的新记录的ID以及被忽略的重复记录的ID。
I'm trying to use merge into to insert only new records. I would like to collect the IDs for new records that got inserted and also the IDs for duplicate records that get ignored.
这是该表的create语句:
Here's the create statement for the table:
drop table SSZ_ME_MIS.test_update_table;
create table ssz_me_mis.test_update_table (
ID_col int not null generated always as identity, -- Primary Key
val_col_1 int not null,
val_col_2 varchar(255) not null,
constraint pk_test_update_table primary key (ID_col),
constraint uq_test_update_table unique (val_col_1, val_col_2)
);
然后填充一些初始值:
insert into ssz_me_mis.test_update_table (val_col_1, val_col_2)
select *
from (values
(231, 'Value 1'),
(481, 'Value 2'),
(813, 'Value 3')
);
最后,我想尝试进行这种插入:
So, finally, I'd like to try to do this sort of insert:
select ID_col from final table (
merge into ssz_me_mis.test_update_table t using (
select *
from (values
(231, 'Value 1'),
(481, 'Value 2'),
(513, 'Value 4')
)
) as s (val_col_1, val_col_2)
on
t.val_col_1 = s.val_col_1
and t.val_col_2 = s.val_col_2
when not matched then
insert (val_col_1, val_col_2)
values (s.val_col_1, s.val_col_2)
else
ignore
);
有什么方法可以做到这一点?
Is there some way to accomplish this?
推荐答案
类似的操作将在Db2 LUW上运行(假设您使用的是 ORGANIZE BY ROW
表)。
Something like this will run on Db2 LUW (assuming you are using ORGANIZE BY ROW
tables).
with s (val_col_1, val_col_2) AS (values
(231, 'Value 1'),
(481, 'Value 2'),
(513, 'Value 4')
)
, i as (select * from final table(
INSERT INTO ssz_me_mis.test_update_table ( val_col_1 , val_col_2)
select * from s where not exists (select 1 from ssz_me_mis.test_update_table t
where
t.val_col_1 = s.val_col_1
and t.val_col_2 = s.val_col_2
)
))
, u as (select count(*) as dummy from new table(
update ssz_me_mis.test_update_table t
set val_col_1 = (select val_col_1 from s where t.val_col_1 = s.val_col_1 and t.val_col_2 = s.val_col_2)
, val_col_2 = (select val_col_2 from s where t.val_col_1 = s.val_col_1 and t.val_col_2 = s.val_col_2)
where exists (select val_col_2 from s where t.val_col_1 = s.val_col_1 and t.val_col_2 = s.val_col_2)
))
select ID_col from i, u
我包括了一个用于更新的分支,但从逻辑上讲,您需要一些非关键列才能使其有意义。您的示例实际上只是一个INSERT,所以我有点困惑为什么您完全使用 MERGE
。
I included a branch for updates, but logically you need some non-key columns for that to make sense. Your example is just an INSERT in practice, so I was a bit confused why you were using MERGE
at all.
这篇关于最终桌的合并金额是多少?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!