Update query teradata with nested select statement

Question

I'm trying to replace a select into another table with same parameters.

UPDATE DL_RG_ANALYTICS.SH_historico_1
FROM
(
    SELECT
        ANO_MES, TIP_TAR,DIA_PAGO, MARCA_RENEG, MONTO, TRAMO_MORA, DIA_PAGO, CASOS, CANAL, TRAMO_PAGO, TIPO_MORA, MES
        ,CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1

    FROM



    (SELECT * FROM DL_RG_ANALYTICS.SH_historico_1 A
            LEFT JOIN 
                (SELECT ANO||MES AS ANOMES, MAX(DIA) AS MAX_DIA FROM DL_RG_ANALYTICS.SH_CALENDARIO
                GROUP BY 1) B
            ON A.ANO_MES = B.ANOMES
    ) M

)

The problem is when I do the update statement, it doest work

The other way it is to create another table with SAME structure and information.. like this

CREATE TABLE DL_RG_ANALYTICS.SH_HISTORICO_2 AS (


SELECT
    ANO_MES, TIP_TAR, MARCA_RENEG, MONTO, TRAMO_MORA, DIA_PAGO, CASOS, CANAL, TRAMO_PAGO, CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1, TIPO_MORA, MES

FROM

(SELECT * FROM DL_RG_ANALYTICS.SH_historico_1 A
        LEFT JOIN 
            (SELECT ANO||MES AS ANOMES, MAX(DIA) AS MAX_DIA FROM DL_RG_ANALYTICS.SH_CALENDARIO
            GROUP BY 1) B
        ON A.ANO_MES = B.ANOMES
) M   

   ) 
WITH DATA;

But this it is not an efficient mode.

Any help is apreciated.

Answer 1

If you want to do an UPDATE you need to use SET to set the values. Try something like:

UPDATE DL_RG_ANALYTICS.SH_historico_1 
FROM (
  SELECT 
    ANO_MES, TIP_TAR,DIA_PAGO, MARCA_RENEG, MONTO, TRAMO_MORA, DIA_PAGO, CASOS, CANAL,
    TRAMO_PAGO, TIPO_MORA, MES,CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1
  FROM (
    SELECT * 
    FROM DL_RG_ANALYTICS.SH_historico_1 A
    LEFT JOIN (
      SELECT ANO||MES AS ANOMES, MAX(DIA) AS MAX_DIA 
      FROM DL_RG_ANALYTICS.SH_CALENDARIO
      GROUP BY 1
    ) B ON A.ANO_MES = B.ANOMES
  ) M
) src -- source rows
SET col1 = src.col1 -- set new value(s)
WHERE SH_historico_1.pk_col = src.pk_col  -- needs to be 1-1

Just make sure your join condition in the WHERE clause is a 1-1 relationship so you don't have multiple source rows trying to update a single target row.