如何在SAP HANA中实现表T1与视图V1的数据合并更新？

现有无主键表T1，结构及数据如下：

另有同结构无主键视图V1，数据如下：

需实现：将T1中与V1匹配（Account_ID、Order_Number、Article_Number完全一致）的记录更新为V1的Price值，同时将V1中T1不存在的记录插入到T1，最终得到目标结果。

方案1：分两步执行（兼容多数数据库） #

先更新匹配的记录，再插入不存在的记录。

更新操作

UPDATE T1
SET Price = V1.Price
FROM T1
JOIN V1 ON T1.Account_ID = V1.Account_ID
       AND T1.Order_Number = V1.Order_Number
       AND T1.Article_Number = V1.Article_Number;

插入操作

INSERT INTO T1 (Account_ID, Order_Number, Article_Number, Price)
SELECT Account_ID, Order_Number, Article_Number, Price
FROM V1
WHERE NOT EXISTS (
    SELECT 1
    FROM T1
    WHERE T1.Account_ID = V1.Account_ID
      AND T1.Order_Number = V1.Order_Number
      AND T1.Article_Number = V1.Article_Number
);

方案2：使用MERGE语句（支持的数据库：SQL Server、Oracle、PostgreSQL 15+） #

用单条语句完成更新+插入的合并操作：

MERGE INTO T1
USING V1
ON (T1.Account_ID = V1.Account_ID
    AND T1.Order_Number = V1.Order_Number
    AND T1.Article_Number = V1.Article_Number)
WHEN MATCHED THEN
    UPDATE SET Price = V1.Price
WHEN NOT MATCHED THEN
    INSERT (Account_ID, Order_Number, Article_Number, Price)
    VALUES (V1.Account_ID, V1.Order_Number, V1.Article_Number, V1.Price);

方案3：MySQL专属实现（需先创建唯一索引） #

MySQL不支持标准MERGE，可通过INSERT ... ON DUPLICATE KEY UPDATE实现：

-- 先给三个字段组合创建唯一索引，用于匹配重复记录
ALTER TABLE T1 ADD UNIQUE INDEX idx_unique_record (Account_ID, Order_Number, Article_Number);

-- 执行插入/更新
INSERT INTO T1 (Account_ID, Order_Number, Article_Number, Price)
SELECT Account_ID, Order_Number, Article_Number, Price
FROM V1
ON DUPLICATE KEY UPDATE Price = VALUES(Price);

注意事项 #

• 由于表和视图无主键，默认Account_ID+Order_Number+Article_Number的组合可唯一标识一条记录，否则可能出现重复更新/插入的异常。
• 不同数据库的语法细节可能有差异，需根据实际使用的数据库调整语句。

内容的提问来源于stack exchange，提问作者user2255207