PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Sunday, October 23, 2022

[FIXED] How to rewrite this sql query, that updates multiple rows in a single column, WITHOUT using CASE

 October 23, 2022     postgresql, sql, sql-update     No comments   

Issue

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 'mod'
                  WHEN column_a = 'balance growth' THEN 'mod growth'  
                  WHEN column_a = 'aggressive'     THEN 'mod/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.


Solution

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
;


Answered By - Jesusbrother
Answer Checked By - Mary Flores (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing