如何解决postgresql sql查询中更新单列中的多行时出现的语法错误

Question

给定tableA在Postgres数据库中，我需要更新字段column_a中的多行:

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

，但我得到以下语法错误:

错误:"，"处或附近有语法错误

Answer 1

您可以使用单个CASE语句:

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;

在这里试试。