sql - 使用 WITH 和 CASE 更新 - PostgreSQL

Question

我正在尝试将列的值更改为根据其他两个表的信息构建的标题，但是我在获取数据时遇到了麻烦。我目前想对所有条目执行此查询 table 。我在 CASE 上遇到语法错误，我不知道为什么。

UPDATE campaigns AS cmp
    SET name = (
        WITH ptn AS (SELECT first_name, last_name FROM politicians WHERE id = cmp.politician_id),
            rc AS (SELECT office FROM races WHERE id = cmp.race_id)

        CASE
            WHEN rc.office IS NULL OR rc.office = '' THEN ptn.first_name || ' ' || ptn.last_name
            ELSE ptn.first_name || ' ' || ptn.last_name || ' for ' || rc.office
        END
    )

这是 PostGres 9.4。这是我遇到的错误

ERROR:  syntax error at or near "case"
LINE 5:   case
          ^

********** Error **********

ERROR: syntax error at or near "case"
SQL state: 42601
Character: 189

Answer 1

出现语法错误是因为您的关联子查询无效。您需要在两个公用表表达式之后添加一些 select 语句:

普通表表达式的基本结构是这样的:

with ptn as (...),
  rc as (...)
select --<< you are missing this select here

但我认为整个事情可以写得更短更有效(如果我没记错的话)

UPDATE campaigns AS cmp
    SET name = CASE
                 WHEN rc.office IS NULL OR rc.office = '' THEN ptn.first_name || ' ' || ptn.last_name
                ELSE ptn.first_name || ' ' || ptn.last_name || ' for ' || rc.office
              END
from politicians ptn, races rc 
where ptn.id = cmp.politician_id
  and rc.id = cmp.race_id