How to rewrite this sql query, that updates multiple rows in a single column, WITHOUT using CASE-CodePudding

I am working on a new project and am trying to update multiple rows in a single column. It appears any query using the CASE syntax is throwing an error. I am needing to rewrite this query so that the CASE syntax is not used. Any help here is appreciated, i have:

UPDATE tableA
SET
  column_a = CASE WHEN column_a = 'conserve'       THEN 'fixed'
                  WHEN column_a = 'balance'        THEN 'moderate'
                  WHEN column_a = 'balance growth' THEN 'moderate growth'  
                  WHEN column_a = 'aggressive'     THEN 'moderate/agressive'
             END;

The error I am seeing in our pipleine is:

Caused by: liquibase.exception.DatabaseException: ERROR: syntax at or near "column_a"

I am looking for alternatives to using CASE when updating multiple rows in a single column.

CodePudding user response：

Not sure what a reason to not use CASE (coz imo it's a more useful option), but here's dbfiddle with a couple of alternatives for the UPDATE statement (REPLACE and CTE):

-- replace example
    UPDATE tableA
    SET
        column_a = REPLACE(REPLACE(REPLACE
        (column_a, 'conserve', 'fixed'),
        'balance', 'moderate'),
        'aggressive','moderate/agressive');

-- CTE example
UPDATE tableA
SET
    column_a = tmp.tmp_val
FROM (
SELECT 'conserve' as tmp_key, 'fixed' as tmp_val
union select 'balance', 'moderate'
union select 'balance growth', 'moderate growth'
union select 'aggressive', 'moderate/agressive'
) tmp
WHERE tmp.tmp_key = tableA.column_a
;

CodePudding user response：

I would write this query like as follows. It's not pritty but should get the job done.

UPDATE tableA
SET column_a = REPLACE(column_a, 'conserve', 'fixed'),
column_a = REPLACE(column_a, 'balance', 'moderate'),
...