Redshift - Updating a specific column in Redshift DB

Question

I have a sales table that stores the sale data. This table gets updated once every day (incremental update). I am trying to update one of the columns in this table to calculate the aging since the sale_date.

update sales set aging = (select CASE WHEN sale_date > current_date-28 AND sale_date < current_date -14 THEN '2 - 4 Weeks Ago'
                    WHEN terminated_at > current_date-14 AND terminated_at < current_date THEN '0 - 2 Weeks Ago'
                    WHEN terminated_at < current_date-28 THEN '4+ Weeks Ago' ELSE 'Unknown' end as aging from sales) ;

I get Invalid operation : invalid query error. Could anyone help me find where am I going wrong with this.

I am using a Amazon Redshift DB.

Thanks.

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-07-31T10:49:58

You probably can just remove the select on the RHS side of your update:

UPDATE sales
SET aging = CASE WHEN sale_date > current_date - 28 AND sale_date < current_date - 14
                 THEN '2 - 4 Weeks Ago'
                 WHEN terminated_at > current_date - 14 AND terminated_at < current_date
                 THEN '0 - 2 Weeks Ago'
                 WHEN terminated_at < current_date - 28
                 THEN '4+ Weeks Ago'
                 ELSE 'Unknown' END;

But, I feel that perhaps you should not be storing this information in your SQL table. Rather, you can generate this output when you select using the same CASE expression whenever you need it. The reason I say this is because as you add new records, and therefore new date information, this computed column would have to be calculated again, and this can be costly.

Redshift - Updating a specific column in Redshift DB

1 Answers