PostgreSQL：如何基于双主键使用另一张表实现UPSERT？

嘿，你要的其实是「Upsert」操作——也就是同时搞定已有数据更新和新数据插入，光用UPDATE肯定覆盖不了新增场景，得用数据库专门的语法来实现。不同数据库的写法略有差异，我给你分情况说清楚：

PostgreSQL 写法 #

PostgreSQL原生支持ON CONFLICT语法，刚好适配你的双主键场景：

INSERT INTO OLD_DATA (first_name, last_name, val_1, val_2, val_3, ...)
SELECT first_name, last_name, val_1, val_2, val_3, ...
FROM NEW_DATA
ON CONFLICT (first_name, last_name) DO UPDATE
SET val_1 = EXCLUDED.val_1,
    val_2 = EXCLUDED.val_2,
    val_3 = EXCLUDED.val_3,
    -- 把剩下的所有非主键字段都按这个格式补上
    ...;

这里的EXCLUDED是PostgreSQL的关键字，代表冲突时原本要插入的那条新数据，用它来赋值比再次关联NEW_DATA表更高效。

MySQL 写法 #

如果是MySQL，只要你的first_name和last_name已经被设为联合主键或者唯一索引，就可以用ON DUPLICATE KEY UPDATE：

INSERT INTO OLD_DATA (first_name, last_name, val_1, val_2, val_3, ...)
SELECT first_name, last_name, val_1, val_2, val_3, ...
FROM NEW_DATA
ON DUPLICATE KEY UPDATE
val_1 = VALUES(val_1),
val_2 = VALUES(val_2),
val_3 = VALUES(val_3),
-- 其他非主键字段依次补上
...;

VALUES(val_1)会取插入语句中对应字段的新值，逻辑和PostgreSQL的EXCLUDED类似。

SQL Server 写法 #

SQL Server用MERGE语法来实现Upsert，逻辑更直观：

MERGE INTO OLD_DATA AS target
USING NEW_DATA AS source
ON target.first_name = source.first_name AND target.last_name = source.last_name
WHEN MATCHED THEN
    UPDATE SET
        target.val_1 = source.val_1,
        target.val_2 = source.val_2,
        target.val_3 = source.val_3,
        -- 补上其他需要更新的字段
        ...
WHEN NOT MATCHED THEN
    INSERT (first_name, last_name, val_1, val_2, val_3, ...)
    VALUES (source.first_name, source.last_name, source.val_1, source.val_2, source.val_3, ...);

这段代码的逻辑很清晰：匹配到双主键就更新，没匹配到就插入。

几个重要注意事项 #

• 一定要确保first_name和last_name在OLD_DATA表上被定义为主键或者唯一约束/索引，不然数据库没法检测冲突，Upsert会失效。
• 字段比较多的话，别漏写任何需要插入或更新的字段，最好把所有非主键字段都明确列出来，避免出现默认值或NULL的意外问题。
• 如果数据量特别大，建议分批执行操作，避免长时间锁表影响数据库性能。

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