No more spool space in Teradata while trying Update, select with case

Question

I'm trying to update a table with to many rows 388.000.

This is the query:

UPDATE DL_RG_ANALYTICS.SH_HISTORICO
        FROM 
        (
SELECT TIPO_MORA,
CASE TRAMOMORA
    WHEN '0' THEN 'VIGENTE'
    WHEN '1' THEN 'MORA BLANDA'
    WHEN '2' THEN 'MORA BLANDA'
    WHEN '3' THEN 'MORA BLANDA'
    WHEN '4' THEN 'MORA DURA'
    WHEN '5' THEN 'MORA DURA'
    WHEN '6' THEN 'MORA DURA'
    ELSE 'VIGENTE'
    END AS A
FROM DL_RG_ANALYTICS.SH_historico
) X
SET  TIPO_MORA = A

I did an update with multiple columns, but I don't know how to do it just with one, and a case inside a select.

Any help is apreciate.

Answer 1

you can not update the same table while reading it as a subquery. Also, usually, if you are joining two tables is good to write a joiner in the where clause otherwise you get a cartesian product join and execution plan nightmare.

Try with this:

UPDATE DL_RG_ANALYTICS.SH_HISTORICO
SET
TIPO_MORA = CASE TRAMOMORA WHEN '0' THEN 'VIGENTE'
                           WHEN '1' THEN 'MORA BLANDA'
                           WHEN '2' THEN 'MORA BLANDA'
                           WHEN '3' THEN 'MORA BLANDA'
                           WHEN '4' THEN 'MORA DURA'
                           WHEN '5' THEN 'MORA DURA'
                           WHEN '6' THEN 'MORA DURA'
                           ELSE 'VIGENTE'
            END;

I hope it helps.