合并的决赛桌等价物是什么? [英] What is the Final Table equivalent for Merge?

问题描述

我正在尝试使用合并到仅插入新记录.我想收集插入的新记录的 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.

这是表的创建语句:

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)
);

然后，填充一些初始值:

and then, to populate some initial values:

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屋！