MERGE语句产生多个"Invalid column name“

Question

我尝试在以下过程中使用MERGE语句：

BEGIN

    MERGE INTO dm_udm.D_ANOMALY_TYPES_CATEGORY AS DIM
    USING (SELECT ID AS ID_ANOMALY_TYPES_CATEGORY
            ,AnomalyType
            ,AnomalyCategoryID
            ,AnomalyTypeDesc
        FROM src_udm.AnomalyTypesCategory
    )AS STG (
             ID_ANOMALY_TYPES_CATEGORY
            ,DES_ANOMALY_TYPE
            ,ID_ANOMALY_CATEGORY
            ,DES_ANOMALY_TYPE_DESC
            )
    ON DIM.ID_ANOMALY_TYPES_CATEGORY=STG.ID_ANOMALY_TYPES_CATEGORY
    WHEN MATCHED
        THEN UPDATE SET                     
        DIM.ID_ANOMALY_TYPES_CATEGORY           =   STG.ID_ANOMALY_TYPES_CATEGORY
        ,DIM.DES_ANOMALY_TYPE                   =   STG.AnomalyType
        ,DIM.ID_ANOMALY_CATEGORY                =   STG.AnomalyCategoryID
        ,DIM.DES_ANOMALY_TYPE_DESC              =   STG.AnomalyTypeDesc
    WHEN NOT MATCHED BY TARGET
    THEN INSERT
        (
            ID_ANOMALY_TYPES_CATEGORY   
            ,DES_ANOMALY_TYPE           
            ,ID_ANOMALY_CATEGORY            
            ,DES_ANOMALY_TYPE_DESC      
        )
    VALUES
        (
            STG.ID_ANOMALY_TYPES_CATEGORY
            ,STG.AnomalyType
            ,STG.AnomalyCategoryID
            ,STG.AnomalyTypeDesc
        );
END

但我在过程执行后收到以下错误：

Invalid column name 'AnomalyCategoryID'.
Invalid column name 'AnomalyCategoryID'.
Invalid column name 'AnomalyTypeDesc'.
Invalid column name 'AnomalyTypeDesc'.
Invalid column name 'AnomalyType'.
Invalid column name 'AnomalyType'.

似乎所有的别名都是正确的，我不明白为什么我会得到这个错误。

你有什么办法解决这个问题吗？提前谢谢。

Answer 1

问题是您在作为STG之后使用别名，请尝试以下操作：

BEGIN

    MERGE INTO dm_udm.D_ANOMALY_TYPES_CATEGORY AS DIM
    USING (SELECT ID AS ID_ANOMALY_TYPES_CATEGORY
            ,AnomalyType
            ,AnomalyCategoryID
            ,AnomalyTypeDesc
        FROM src_udm.AnomalyTypesCategory
    )AS STG 
    ON DIM.ID_ANOMALY_TYPES_CATEGORY=STG.ID_ANOMALY_TYPES_CATEGORY
    WHEN MATCHED
        THEN UPDATE SET                     
        DIM.ID_ANOMALY_TYPES_CATEGORY           =   STG.ID_ANOMALY_TYPES_CATEGORY
        ,DIM.DES_ANOMALY_TYPE                   =   STG.AnomalyType
        ,DIM.ID_ANOMALY_CATEGORY                =   STG.AnomalyCategoryID
        ,DIM.DES_ANOMALY_TYPE_DESC              =   STG.AnomalyTypeDesc
    WHEN NOT MATCHED BY TARGET
    THEN INSERT
        (
            ID_ANOMALY_TYPES_CATEGORY   
            ,DES_ANOMALY_TYPE           
            ,ID_ANOMALY_CATEGORY            
            ,DES_ANOMALY_TYPE_DESC      
        )
    VALUES
        (
            STG.ID_ANOMALY_TYPES_CATEGORY
            ,STG.AnomalyType
            ,STG.AnomalyCategoryID
            ,STG.AnomalyTypeDesc
        );
END