如何在Amazon Redshift中使用临时表字段更新现有表列并解决相关报错

错误原因 #

• Redshift的UPDATE语法要求别名必须与表名绑定声明，原语句直接在UPDATE后使用未关联表的别名C，因此触发relation "c" does not exist报错。
• 原SQL使用+做字符串拼接，Redshift中字符串拼接需使用||运算符，否则会被识别为数值运算触发类型错误。

适配Redshift的重写代码 #

WITH TempTable AS
(
        SELECT   LEFT('abcdefghijk', LEN(TerritoryName)/3) ||  SUBSTRING(TerritoryName, LEN(TerritoryName)-LEN(TerritoryName)/3-LEN(TerritoryName)/3+1, LEN(TerritoryName)-LEN(TerritoryName)/3-LEN(TerritoryName)/3) || RIGHT('ijklmnopqrstuv', LEN(TerritoryName)/3) AS Masked_TerritoryName
                ,LEFT('abcdefghijk', LEN(DistrictName)/3) ||  SUBSTRING(DistrictName, LEN(DistrictName)-LEN(DistrictName)/3-LEN(DistrictName)/3+1, LEN(DistrictName)-LEN(DistrictName)/3-LEN(DistrictName)/3) || RIGHT('ijklmnopqrstuv', LEN(DistrictName)/3) AS Masked_DistrictName
                ,LEFT('abcdefghijk', LEN(RegionName)/3) ||  SUBSTRING(RegionName, LEN(RegionName)-LEN(RegionName)/3-LEN(RegionName)/3+1, LEN(RegionName)-LEN(RegionName)/3-LEN(RegionName)/3) || RIGHT('ijklmnopqrstuv', LEN(RegionName)/3) AS Masked_RegionName
                ,LEFT('abcdefghijk', LEN(RSMTerritoryName)/3) ||  SUBSTRING(RSMTerritoryName, LEN(RSMTerritoryName)-LEN(RSMTerritoryName)/3-LEN(RSMTerritoryName)/3+1, LEN(RSMTerritoryName)-LEN(RSMTerritoryName)/3-LEN(RSMTerritoryName)/3) || RIGHT('ijklmnopqrstuv', LEN(RSMTerritoryName)/3) AS Masked_RSMTerritoryName
                ,LEFT('abcdefghijk', LEN(CCAName)/3) ||  SUBSTRING(CCAName, LEN(CCAName)-LEN(CCAName)/3-LEN(CCAName)/3+1, LEN(CCAName)-LEN(CCAName)/3-LEN(CCAName)/3) || RIGHT('ijklmnopqrstuv', LEN(CCAName)/3) AS Masked_CCAName
                ,LEFT('abcdefghijk', LEN(LCAName)/3) ||  SUBSTRING(LCAName, LEN(LCAName)-LEN(LCAName)/3-LEN(LCAName)/3+1, LEN(LCAName)-LEN(LCAName)/3-LEN(LCAName)/3) || RIGHT('ijklmnopqrstuv', LEN(LCAName)/3) AS Masked_LCAName
                ,LEFT('abcdefghijk', LEN(TMComp)/3) ||  SUBSTRING(TMComp, LEN(TMComp)-LEN(TMComp)/3-LEN(TMComp)/3+1, LEN(TMComp)-LEN(TMComp)/3-LEN(TMComp)/3) || RIGHT('ijklmnopqrstuv', LEN(TMComp)/3) AS Masked_TMComp
                ,LEFT('abcdefghijk', LEN(ASMTerritoryName)/3) ||  SUBSTRING(ASMTerritoryName, LEN(ASMTerritoryName)-LEN(ASMTerritoryName)/3-LEN(ASMTerritoryName)/3+1, LEN(ASMTerritoryName)-LEN(ASMTerritoryName)/3-LEN(ASMTerritoryName)/3) || RIGHT('ijklmnopqrstuv', LEN(ASMTerritoryName)/3) AS Masked_ASMTerritoryName
                ,TerritoryCode      
        FROM TargetTable
)
UPDATE TargetTable AS C
SET      C.TerritoryName = N.Masked_TerritoryName
        ,C.DistrictName = N.Masked_DistrictName
        ,C.RegionName = N.Masked_RegionName
        ,C.RSMTerritoryName = N.Masked_RSMTerritoryName
        ,C.CCAName = N.Masked_CCAName
        ,C.LCAName = N.Masked_LCAName
        ,C.TMComp = N.Masked_TMComp
        ,C.ASMTerritoryName = N.Masked_ASMTerritoryName
FROM TempTable N 
WHERE C.TerritoryCode = N.TerritoryCode;

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