如何使用update命令基于同结构相似表更新目标表数据

两个表的唯一业务标识为ID和date的联合字段，相同ID+date视为同一条记录，同步逻辑分为更新已有记录、插入新增记录两部分，不同数据库的实现语法如下：

1. 仅更新table1中已存在的匹配记录 #

MySQL 写法 #

UPDATE table1 t1
INNER JOIN table2 t2 
ON t1.ID = t2.ID AND t1.date = t2.date
SET 
  t1.value1 = t2.value1,
  t1.value2 = t2.value2,
  t1.value3 = t2.value3,
  t1.value4 = t2.value4;

PostgreSQL 写法 #

UPDATE table1 t1
SET 
  value1 = t2.value1,
  value2 = t2.value2,
  value3 = t2.value3,
  value4 = t2.value4
FROM table2 t2
WHERE t1.ID = t2.ID AND t1.date = t2.date;

SQL Server 写法 #

UPDATE t1
SET 
  t1.value1 = t2.value1,
  t1.value2 = t2.value2,
  t1.value3 = t2.value3,
  t1.value4 = t2.value4
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.ID AND t1.date = t2.date;

2. 插入table2中存在但table1不存在的新增记录 #

INSERT INTO table1 (ID, date, value1, value2, value3, value4)
SELECT t2.ID, t2.date, t2.value1, t2.value2, t2.value3, t2.value4
FROM table2 t2
LEFT JOIN table1 t1 
ON t1.ID = t2.ID AND t1.date = t2.date
WHERE t1.ID IS NULL;

3. 单语句完成更新+新增（Upsert，适用于MySQL） #

需要先给table1的ID和date字段添加联合唯一索引，即可自动完成已有记录更新、新增记录插入的操作：

-- 仅首次执行，添加联合唯一索引
ALTER TABLE table1 ADD UNIQUE KEY idx_id_date (ID, date);

-- 执行同步操作
INSERT INTO table1 (ID, date, value1, value2, value3, value4)
SELECT ID, date, value1, value2, value3, value4 FROM table2
ON DUPLICATE KEY UPDATE
  value1 = VALUES(value1),
  value2 = VALUES(value2),
  value3 = VALUES(value3),
  value4 = VALUES(value4);

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