基于关联表更新TableB的key字段及子查询报错解决

现有两张数据表tableA和TableB，表结构及测试数据如下：

create table tableA(
key number(38,0),
tab1_uid varchar(500),
as_of_date date
)

create table TableB(
key number(38,0),
tab2_uid varchar(500),
as_of_date date
)

insert into tableA (key,tab1_uid,as_of_date) values (1,'123','2022-08-15'),(2,'345','2022-08-15'),(3,'256','2022-08-15');

insert into TableB (key,tab2_uid,as_of_date) values (0,'123','2022-08-18'),(0,'345','2022-08-18'),(1,'123','2022-08-17'),(2,'345','2022-08-17');

需求：当TableB的key为0且tab2_uid与tableA的tab1_uid匹配时，将TableB的key字段更新为tableA对应的key值。

尝试执行以下SQL时报错Unsupported subquery type cannot be evaluated：

update tableB a 
set participant_key = (
  select b.key from tableA b 
  where a.tab2_uid = b.tab1_uid 
  and a.key = 0 and b.as_of_date = '2022-08-18'
)

1. 字段名错误：原语句中set participant_key，但TableB的目标字段是key，属于笔误。
2. 条件匹配错误：tableA的测试数据中as_of_date均为2022-08-15，但子查询限定b.as_of_date = '2022-08-18'，会导致无匹配数据。
3. 子查询兼容性问题：部分数据库不支持这种关联子查询的更新写法，触发语法兼容性报错。

推荐使用MERGE语句实现关联更新，该写法在Oracle、SQL Server等多数关系型数据库中均支持，可避免子查询的兼容性问题：

MERGE INTO TableB tgt
USING (
    SELECT a.key, a.tab1_uid
    FROM tableA a
    -- 根据实际需求调整tableA的日期条件，此处匹配测试数据的日期
    WHERE a.as_of_date = '2022-08-15'
) src
ON (tgt.tab2_uid = src.tab1_uid AND tgt.key = 0)
WHEN MATCHED THEN
    UPDATE SET tgt.key = src.key;

如果你的数据库支持UPDATE JOIN语法（如MySQL、PostgreSQL），也可使用以下写法：

-- MySQL/PostgreSQL 适配写法
UPDATE TableB tgt
JOIN tableA src ON tgt.tab2_uid = src.tab1_uid
SET tgt.key = src.key
WHERE tgt.key = 0 
  AND src.as_of_date = '2022-08-15';

执行上述语句后，TableB中符合条件的记录会被正确更新：

• 原(0,'123','2022-08-18')将更新为(1,'123','2022-08-18')
• 原(0,'345','2022-08-18')将更新为(2,'345','2022-08-18')
• 其余记录保持不变

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