How to update multiple columns in same update statement with one column depends upon another new column new value in Redshift

Question

I want to update multiple columns in same update statement with one column depends upon another new column new value.

Example:

Sample Data: col1 and col2 is the column names and test_update is the table name.

SELECT * FROM test_update;
col1    col2
col-1   col-2
col-1   col-2
col-1   col-2

update test_update set col1 = 'new', col2=col1||'-new';

SELECT * FROM test_update;
col1    col2
new col-1-new
new col-1-new
new col-1-new

What I need to achieve is col2 is updated as new-new as we updated value of col1 is new.

I think may be its not possible in one SQL statement. If possible How can we do that, If its not What is best way of handling this problem in Data Warehouse environment, like execute multiple update 1st on col1 and then on col2 or any other.

Hoping my question is clear.

question from:https://stackoverflow.com/questions/65559570/how-to-update-multiple-columns-in-same-update-statement-with-one-column-depends

Answer 1

You cannot update the second column based on the result of updating the first column. However this can be achieved in a single by "pre-calculating" the result you want and then updating based on that.

The following update using a join is based on the example provided in the Redshift documentation:

UPDATE test_update 
SET col1 = precalc.col1
  , col2 = precalc.col2
FROM (
    SELECT catid
         , 'new' AS col1
         , col1 || '-new' AS col2
    FROM test_update
    ) precalc
WHERE test_update.id = precalc.id;
;