如何在teradata中迁移sql更新查询？

Question

我想迁移下面的查询。我正在更新表中的一些行，并使用sql编写了此查询。现在，我想迁移到下面的脚本中。我听说我们需要使用易失性表来选择数据，然后更新。是否必须先创建易失性表，然后再进行更新？

UPDATE gam 
    SET gam.DeviceActiveInSIGMA = 1,
    gam.DeviceInSIGMA =1,
    gam.AccountServiceInSIGMA =1,
    gam.AccountInSIGMA =1,
    gam.PKG_TYPE = 'VOICE',
    gam.MACType = 'Voice'
  FROM Staging.AllAccountDevices gam
  WITH (INDEX (ix_MAC_UniqueID))
  INNER JOIN dbo.TBL_SIGMA_VOIP_EQUIPMENT dvc
     ON gam.MACAddr = dvc.MTA_MAC
  INNER JOIN dbo.TBL_SIGMA_SUB sac
  ON dvc.SUB_ID = sac.SUB_ID
  AND gam.AccountNumber = sac.Account_Number
  LEFT OUTER JOIN HSIRECON.DBO.NEXTGEN_ACTIVE_SPAS nas 
  ON SUBSTRING(gam.AccountNumber,1,6) = nas.ACCOUNT_START

  WHERE nas.ACCOUNT_START is NULL
  AND gam.DeviceActiveInSIGMA = 0
  AND dvc.STATUS IN ('active','change_in_progress')

Answer 1

您不需要使用易失性表，而需要使用派生表，因为Teradata在更新中不支持外部连接。

与此类似：

UPDATE gam
FROM Staging.AllAccountDevices  AS gam,
 (
   SELECT *  FROM Staging.AllAccountDevices gam
   INNER JOIN dbo.TBL_SIGMA_VOIP_EQUIPMENT dvc
      ON gam.MACAddr = dvc.MTA_MAC
   INNER JOIN dbo.TBL_SIGMA_SUB sac
   ON dvc.SUB_ID = sac.SUB_ID
   AND gam.AccountNumber = sac.Account_Number
   LEFT OUTER JOIN HSIRECON.DBO.NEXTGEN_ACTIVE_SPAS nas 
   ON Substr(gam.AccountNumber,1,6) = nas.ACCOUNT_START
   WHERE nas.ACCOUNT_START IS NULL
   AND gam.DeviceActiveInSIGMA = 0
   AND dvc.STATUS IN ('active','change_in_progress')
 ) AS dt

SET DeviceActiveInSIGMA = 1,
    DeviceInSIGMA =1,
    AccountServiceInSIGMA =1,
    AccountInSIGMA =1,
    PKG_TYPE = 'VOICE',
    MACType = 'Voice'
WHERE gam.pk = dt.pk -- must be the Primary Key columns(s) of Staging.AllAccountDevices
  AND gam.DeviceActiveInSIGMA = 0

顺便说一句，最初的查询是为Server而不是为Oracle编写的。