无法使用PostgreSQL中的CASE表达式更新列值

Question

我试图使用update语句更新表列的一些值。然而，我无法得到更新的标签‘抑制剂’和‘激活剂；抑制剂’在最终输出。

update schema.table
set action_norm = (case 
                   when action in (     
                                         'inhibitor', 
                                          'DNA Inhibitor', 
                                          'RNA Inhibitor', 
                                          'RNA Synthesis Inhibitor', 
                                          'RNA Synthesis Inhibitor') then 'inhibitor'
                                  
                   when action ilike '%agonist; antagoniost%' then 'activator; inhibitor'
                   else action
                   end)

如何修改查询以获得所需的输出？

action                    action_norm
inhibitor                 Inhibitor 
DNA Inhibitor             inhibitor 
RNA Inhibitor             inhibitor 
RNA Synthesis Inhibitor   inhibitor 
RNA Synthesis Inhibitor   inhibitor 
agonist; antagoniost      activator; inhibitor

我得到的输出是：

action                    action_norm
inhibitor                 inhibitor
DNA Inhibitor             DNA Inhibitor
RNA Inhibitor             RNA Synthesis Inhibitor
RNA Synthesis Inhibitor   RNA Synthesis Inhibitor
RNA Synthesis Inhibitor   RNA Synthesis Inhibitor
agonist; antagoniost      agonist; antagoniost 

Answer 1

@rshar看起来像是有一个错误，因为查询

select
case
 when action in (     
                   'inhibitor', 
                   'DNA Inhibitor', 
                   'RNA Inhibitor', 
                   'RNA Synthesis Inhibitor', 
                   'RNA Synthesis Inhibitor') then 'inhibitor'
 when action ilike '%agonist; antagoniost%' then 'activator; inhibitor'
 else action
 end
from (select 'agonist; antagoniost' as action) as t

返回准确的“激活剂；抑制剂”，如您所愿。