基于日期与ID跨表更新插入的Oracle SQL实现及报错解决

表结构与初始数据 #

创建表语句 #

create table test (id number primary key, name varchar2(20), insertion_date date);
create table test2 (id number primary key, name varchar2(20), insertion_date date);

插入初始数据 #

insert into test values (1,'Jay','05-Jan-19');
insert into test values (2,'John','05-Jan-20');

insert into test2 values (1,'Jay','05-Mar-25');
insert into test2 values (2,'John','05-Mar-25');
insert into test2 values (3,'Maria','05-Mar-22');

初始表数据 #

• test表数据：

1   Jay     05-JAN-19
2   John    05-JAN-20

• test2表数据：

1   Jay     05-MAR-25
2   John    05-MAR-25
3   Maria   05-MAR-22

需求说明 #

1. 当test2中对应ID行的insertion_date晚于test表该行的insertion_date时，更新test表的name与insertion_date字段；
2. 将test2中test表不存在的ID对应的行插入test表。

原尝试问题 #

原UPDATE语句因未正确关联test2表导致报错：

update test
set name = case when test.insertion_date < test2.insertion_date then test2.name else test.name end,
    insertion_date = case when test.insertion_date < test2.insertion_date then test2.insertion_date else test.insertion_date end,
where test.id = test2.id;

报错信息：

Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 -  "invalid user.table.column, table.column, or column specification"
*Cause:    
*Action:

注：原INSERT语句可正常执行，但无法与更新合并为单语句：

insert into test select * from test2 where id not in (select id from test);

单语句解决方案（MERGE） #

使用Oracle的MERGE语句可同时完成更新与插入操作，满足需求：

MERGE INTO test t
USING test2 t2
ON (t.id = t2.id)
WHEN MATCHED THEN
  UPDATE SET
    t.name = t2.name,
    t.insertion_date = t2.insertion_date
  WHERE t.insertion_date < t2.insertion_date
WHEN NOT MATCHED THEN
  INSERT (id, name, insertion_date)
  VALUES (t2.id, t2.name, t2.insertion_date);

语句说明 #

• MERGE INTO test t：指定目标操作表为test，别名t；
• USING test2 t2：指定数据源表为test2，别名t2；
• ON (t.id = t2.id)：以ID作为匹配关联条件；
• WHEN MATCHED THEN UPDATE：ID匹配时，仅在test的insertion_date早于test2时更新对应字段；
• WHEN NOT MATCHED THEN INSERT：ID不匹配（test中无此ID）时，插入test2的对应行。

执行后test表结果 #

1   Jay     05-MAR-25
2   John    05-MAR-25
3   Maria   05-MAR-22

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