Oracle私有临时表创建报错，求无临时表的requestId批量更新方案

现有Oracle数据库中的TableA数据如下：

TableA
 
 Id   batchId  requestId Filename
 100  Btch100   Rqt1     ABc.zip
 101  Btch100   Null     Abc213.txt
 102  Btch100   Null     Abc5673.pdf
 103  Btch100   Null     Afdc213.txt
 104  Btch101   Rqt2     Abc213.txt
 105  Btch101   Null     Bbc213.txt
 106  Btch102   Null     zbc213.txt
 107  Btch102   Rqt3     z6gt213.txt

需求：将相同batchId的行的requestId统一更新为该batchId下非空的requestId值，预期结果如下：

Id   batchId  requestId Filename
 100  Btch100   Rqt1     ABc.zip
 101  Btch100   Rqt1     Abc213.txt
 102  Btch100   Rqt1     Abc5673.pdf
 103  Btch100   Rqt1     Afdc213.txt
 104  Btch101   Rqt2     Abc213.txt
 105  Btch101   Rqt2     Bbc213.txt
 106  Btch102   Rqt3     zbc213.txt
 107  Btch102   Rqt3     z6gt213.txt

尝试创建私有临时表时执行以下语句：

CREATE PRIVATE TEMPORARY TABLE temptable AS
SELECT requestId,batchId FROM TableA WHERE requestid is not null  group by requestId,batchId .

出现报错：

Error : Unknown objectType 'Private' key word used in Create,Drop,or Alter statement.

需要提供无需使用临时表的更新方案。

方案一：关联子查询更新 #

通过子查询获取每个batchId对应的非空requestId，直接关联更新空值行：

UPDATE TableA t1
SET requestId = (
    SELECT requestId
    FROM TableA t2
    WHERE t2.batchId = t1.batchId
      AND t2.requestId IS NOT NULL
    FETCH FIRST 1 ROW ONLY -- 确保取到唯一非空值，假设每个batchId仅存在一个非空requestId
)
WHERE t1.requestId IS NULL;

注：若单个batchId下存在多个不同的非空requestId，需确认业务规则，可将子查询改为SELECT MAX(requestId)或SELECT MIN(requestId)来固定取值逻辑。

方案二：MERGE语句更新 #

用MERGE操作匹配batchId，批量更新空的requestId：

MERGE INTO TableA t1
USING (
    SELECT batchId, MAX(requestId) AS target_requestId -- 用MAX/MIN保证每个batchId对应唯一值
    FROM TableA
    WHERE requestId IS NOT NULL
    GROUP BY batchId
) t2
ON (t1.batchId = t2.batchId AND t1.requestId IS NULL)
WHEN MATCHED THEN
    UPDATE SET t1.requestId = t2.target_requestId;

方案三：窗口函数更新（Oracle 12c+适用） #

利用FIRST_VALUE窗口函数直接提取每个batchId的非空requestId进行更新：

UPDATE TableA
SET requestId = (
    SELECT FIRST_VALUE(requestId IGNORE NULLS) OVER (PARTITION BY batchId)
    FROM TableA t2
    WHERE t2.batchId = TableA.batchId
    FETCH FIRST 1 ROW ONLY
)
WHERE requestId IS NULL;

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