SQL 使用 WITH 和 CASE 更新 - PostgreSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35142129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE with WITH and CASE - PostgreSQL
提问by cfatt10
I'm trying to change the values of a column to be a title constructed from info from two other tables, however I'm running into trouble getting the data in. I currently want to execute this query on all entries to the table. I'm getting a syntax error on CASE and I can't figure out why.
我正在尝试将列的值更改为根据来自其他两个表的信息构造的标题，但是我在获取数据时遇到了麻烦。我目前想对表的所有条目执行此查询。我在 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
)
This is PostGres 9.4. Here's the error I'm getting
这是 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
回答by a_horse_with_no_name
The syntax error occurs because your co-related subquery isn't valid. You need to have some selectstatement after the two common table expressions:
出现语法错误是因为您的相关子查询无效。您需要select在两个公共表表达式之后添加一些语句：
The basic structure of a common table expression is this:
公用表表达式的基本结构是这样的：
with ptn as (...),
rc as (...)
select --<< you are missing this select here
But I think the whole thing can be written shorter and more efficiently (if I'm not mistaken)
但我认为整个事情可以写得更短更有效（如果我没记错的话）
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
回答by Gordon Linoff
I would be inclined to do this with fromclause:
我倾向于用from子句做到这一点：
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 ;
