当行被 FK 引用时执行 UPSERT

Question

假设我有一个项目表，对于每个项目，可以为其存储附加信息，这些信息会进入第二个表。附加信息由第一个表中的 FK 引用，该 FK 可以为 NULL（如果该项目没有附加信息）。

TABLE item (
    ...
    item_addtl_info_id INTEGER
)

CONSTRAINT fk_item_addtl_info FOREIGN KEY (item_addtl_info)
    REFERENCES addtl_info (addtl_info_id)

TABLE addtl_info (
    addtl_info_id INTEGER NOT NULL
    GENERATED BY DEFAULT 
    AS IDENTITY (
        INCREMENT BY 1
        NO CACHE
        ),
    addtl_info_text VARCHAR(100)
    ...
    CONSTRAINT pk_addtl_info PRIMARY KEY (addtl_info_id)
)

更新项目附加信息的“最佳实践”是什么（最好是在 IBM DB2 SQL 中）？

它应该是一个 UPSERT 操作，这意味着如果附加信息尚不存在，则在第二个表中创建一条新记录，但如果存在，则仅更新它，第一个表中的 FK 不会更改。

因此，这就是逻辑：

UPSERT(item, item_info):
CASE WHEN item.item_addtl_info_id IS NULL THEN
    INSERT INTO addtl_info (item_info)
    UPDATE item.item_addtl_info_id (addtl_info.addtl_info_id)
                                               ^^^^^^^^^^^^^
ELSE
    UPDATE addtl_info (item_info)
END

我的主要问题是如何获取新插入的 addtl_info 行的 id（上面下划线）。在存储过程中，我可以从序列中请求 id 并将其存储在变量中，但也许有更直接的方法。这不是在数据库编程时经常出现的问题吗？

我的意思是，我真的对 addtl_info 记录的 id 不感兴趣，只要它保持唯一并且被正确引用即可。因此，在这种情况下，使用序列对我来说似乎有点矫枉过正。

事实上，这个 UPSERT 操作应该是 SQL 语言的一部分作为标准操作（也许是，但我只是不知道？）...

Answer 1

我正在寻找的语法是：

SELECT * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) )

来自维基百科（http://en.wikipedia.org/ wiki/Insert_%28SQL%29)

这是如何引用刚刚插入表中的记录。

我的同事将此构造称为“就地触发器”，它的真正含义是...

这是我作为复合 SQL 语句组合在一起的第一个版本：

begin atomic
declare addtl_id integer;
set addtl_id = (select item_addtl_info_id from item where item.item_id = XXX);
if addtl_id is null
then
    set addtl_id = (select addtl_info_id from new table 
                    (insert into addtl_info
                        (addtl_info_text)
                        values ('My brand new additional info')
                    )
                  );
    update item set item.item_addtl_info_id = addtl_id
    where item.item_id = XXX;

else
   update addtl_info set addtl_info_text = 'My updated additional info'
   where addtl_info.addtl_info_id = addtl_id;
end if;
end

XXX 等于项目 id待更新 - 现在可以轻松地将代码插入到存储过程中，并且可以将 XXX 转换为输入参数。

我还尝试使用 MERGE INTO，但我无法找出用于更新与指定目标不同的表的语法。