TDWM筛选器空格:错误的无约束产品连接

Question

我正在编写一个update语句，其中我必须用正确的ids更新不正确的ids。下面是我的代码:在易失性表中获取正确和错误的代码：

    CREATE VOLATILE TABLE VT
AS
(
SELECT 
   NAME
   ,DESC
   ,FIRST_VALUE(CODE) 
    OVER (PARTITION BY NAME,DESC,SRC_TYPE_CD 
          ORDER BY CODE) AS CORRECT_CODE
   ,CODE AS INCORRECT_CODE
   ,SRC_TYPE_CD
FROM DB.CPT
QUALIFY
   COUNT(*) 
   OVER (PARTITION BY NAME,DESC,SRC_TYPE_CD 
         ORDER BY CODE
         ROWS UNBOUNDED PRECEDING) > 1
         ) WITH DATA 
ON COMMIT PRESERVE ROWS;

创建一个与原始表格类似的表格用于测试：

CREATE  TABLE DP_DB.CE_TEST AS 
(SELECT * FROM DB.EVNT WHERE 
SRC_TYPE_CD='CRM' and CODE is not null)
WITH DATA AND STATS
ON COMMIT PRESERVE ROWS;

使用易失性表更新CE_TEST：

update a from DP_DB.CE_TEST a, vt b
set CODE= b.CORRECT_CODE where 
a.CODE in ( select b.INCORRECT_CODE from VT)

上面的更新由于产品连接冲突而失败。我在这两个表中没有任何其他匹配的列。我还能做些什么来优化这个查询呢？

以下是来自VT的示例：

Name               Desc CORRECT_CODE    INCORRECT_CODE  SRC_TYPE_CD
700 Calls Service   100 592              601    CRM
700 Calls Service   100 592              595    CRM
700 Calls Service   100 592              597    CRM
700 Calls Service   100 592              598    CRM
700 Calls Service   100 592              594    CRM

谢谢,

阿米特

Answer 1

当目标行被多个源行更新时，你可能仍然会遇到错误(你需要去掉VT中重复的正确/不正确的组合，因为你以后不会限定其他列，比如name、desc等)。

update a from DP_DB.CE_TEST a, vt b
set CODE= b.CORRECT_CODE where 
a.CODE in ( select b.INCORRECT_CODE from VT)

可能应该是

update a from DP_DB.CE_TEST a, vt b
set CODE= b.CORRECT_CODE
where a.CODE = b.INCORRECT_CODE