Teradata目标表存在重复行仅更新单行，报错derived table not allowed如何解决

错误原因 #

你遇到的derived table not allowed for update报错，是因为你SQL里要更新的TGT是子查询生成的派生临时表，不是物理存储的TARGET_TABLE表，数据库不支持直接对派生表执行更新操作。

解决方案 #

方案1：MERGE语句（通用兼容方案） #

MERGE是专门用于跨表更新的语法，不会触发派生表更新限制，支持所有支持窗口函数的数据库：

MERGE INTO TARGET_TABLE TGT
USING (
  -- 关联源表和目标表的待更新行
  SELECT 
    T_MARK.Employee_Id,
    T_MARK.Join_date,
    T_MARK.Status,
    T_MARK.Eartned_points,
    SRC.used_vocher_cnt AS new_cnt
  FROM (
    SELECT *
    FROM TARGET_TABLE
    QUALIFY ROW_NUMBER() OVER(PARTITION BY Employee_id, Join_date, Status ORDER BY Eartned_points DESC) = 1
  ) T_MARK
  INNER JOIN SOURCE_TABLE SRC
    ON T_MARK.Employee_Id = SRC.Employee_Id
    AND T_MARK.Join_date = SRC.Join_date
    AND T_MARK.Status = SRC.Status
) UP_SRC
-- 定位到物理目标表中需要更新的唯一行
ON TGT.Employee_Id = UP_SRC.Employee_Id
AND TGT.Join_date = UP_SRC.Join_date
AND TGT.Status = UP_SRC.Status
AND TGT.Eartned_points = UP_SRC.Eartned_points
WHEN MATCHED THEN UPDATE SET TGT.used_vocher_cnt = UP_SRC.new_cnt;

方案2：Teradata专属简化写法 #

如果你使用的是Teradata数据库，支持UPDATE语句直接加QUALIFY过滤，可以用更简洁的写法：

UPDATE TARGET_TABLE TGT
FROM SOURCE_TABLE SRC
SET TGT.used_vocher_cnt = SRC.used_vocher_cnt
WHERE TGT.Employee_Id = SRC.Employee_Id
AND TGT.Join_date = SRC.Join_date
AND TGT.Status = SRC.Status
QUALIFY ROW_NUMBER() OVER(PARTITION BY TGT.Employee_id, TGT.Join_date, TGT.Status ORDER BY TGT.Eartned_points DESC) = 1;

补充说明 #

• 如果不需要固定选积分最高的行更新，把窗口函数的ORDER BY Eartned_points DESC改成ORDER BY 1即可随机选取分组内任意一行更新
• 如果目标表存在主键/唯一行标识字段，把关联条件中的Eartned_points替换为主键字段，可避免字段值重复导致的误更新

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