SQL - 使用 CASE 语句更新，是否需要多次重复相同的 CASE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5548927/
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
SQL - Update with a CASE statement, do I need to repeat the same CASE multiple times?
提问by rishijd
My UPDATE statement goes along the lines of:
我的 UPDATE 语句是这样的：
UPDATE customer
SET forenames=ot.forenames,
surname =
CASE WHEN ot.safeplace IS NULL
THEN 'test SAFEPLACE IS NULL'
ELSE 'test Safeplace IS NOT NULL'
END,
middlename =
CASE WHEN ot.safeplace IS NULL
THEN 'test2 SAFEPLACE IS NULL'
ELSE 'test2 Safeplace IS NOT NULL'
END,
FROM order_transaction ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
AND customer.custid = 1234
The above works. It basically checks if a field in another table is NULL or not, and then updates the customer's "surname" and "middlename" accordingly. As you can see above, I have repeated the same CASE statement twice. My question is - is there a way I can specify the CASE statement just once?
以上工作。它基本上检查另一个表中的字段是否为 NULL，然后相应地更新客户的“姓氏”和“中间名”。正如你在上面看到的，我重复了两次相同的 CASE 语句。我的问题是 - 有没有办法只指定一次 CASE 语句？
The point is, if I say wanted to update 10 fields based on a certain condition, do I need to include 10 similar CASE conditions? Or can the SQL be improved to have just one CASE, and 10 field updates within the WHEN / ELSE clauses?
关键是，如果我说要根据某个条件更新 10 个字段，我是否需要包含 10 个类似的 CASE 条件？或者可以改进 SQL 以在 WHEN / ELSE 子句中只有一个 CASE 和 10 个字段更新？
(I'm using a Postgresql 8.2 database but I believe the above is standard SQL).
（我使用的是 Postgresql 8.2 数据库，但我相信以上是标准 SQL）。
Many thanks, Rishi
非常感谢，里希
回答by onedaywhen
I believe the above is standard SQL
我相信以上是标准的 SQL
Actually, it isn't. Standard SQL does not have a UPDATE..FROMsyntax. Rather, you need to use a scalar subquery for each SETclause plus another for EXISTS, so the Standard syntax is even more repetitive e.g.
事实上，它不是。标准 SQL 没有UPDATE..FROM语法。相反，您需要为每个SET子句加上另一个 for使用一个标量子查询EXISTS，因此标准语法更加重复，例如
UPDATE customer
SET forenames = (
SELECT ot.forenames
FROM order_transaction AS ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
),
surname = (
SELECT CASE
WHEN ot.safeplace IS NULL
THEN 'test SAFEPLACE IS NULL'
ELSE 'test Safeplace IS NOT NULL'
END
FROM order_transaction AS ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
),
middlename = (
SELECT CASE
WHEN ot.safeplace IS NULL
THEN 'test SAFEPLACE IS NULL'
ELSE 'test Safeplace IS NOT NULL'
END
FROM order_transaction AS ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
)
WHERE customer.custid = 1234
AND EXISTS (
SELECT *
FROM order_transaction AS ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
);
While the syntax looks repetitive, a good optimizer should be able to recognize the repetition and optimize accordingly. Whether the current version of your SQL product actually does a good job of optimizing this in practise is of course another matter. But consider this: if your SQL product of choice supports the Standard syntax but doesn't actually optimize it properly is the "support" worth anything?
虽然语法看起来重复，但一个好的优化器应该能够识别重复并相应地进行优化。您的 SQL 产品的当前版本是否确实在实践中很好地优化了这一点当然是另一回事。但请考虑一下：如果您选择的 SQL 产品支持标准语法，但实际上并没有正确优化它，那么“支持”是否值得？
If you are looking to use Standard SQL (as you indeed should IMO :) and want a more "compact" syntax then take a look at MERGEor MERGE (SQL)e.g. could look more like this:
如果您希望使用标准 SQL（正如您确实应该 IMO :) 并想要更“紧凑”的语法，那么请查看MERGE或MERGE (SQL)例如可能看起来更像这样：
MERGE INTO customer
USING (
SELECT ot.custid, ot.forenames,
CASE
WHEN ot.safeplace IS NULL
THEN 'test SAFEPLACE IS NULL'
ELSE 'test Safeplace IS NOT NULL'
END
FROM order_transaction AS ot
WHERE ot.trans_orderid = 5678
) AS source (custid, forenames, safeplace_narrative)
ON customer.custid = source.custid
AND customer.custid = 1234
WHEN MATCHED THEN
UPDATE
SET forenames = source.forenames,
surname = source.safeplace_narrative,
middlename = source.safeplace_narrative;
回答by RichardTheKiwi
If you wanted to perform the CASE on the same query level, you would need to repeat the CASE, just as you would repeat a computed column in a group by clause.
如果要在同一查询级别执行 CASE，则需要重复 CASE，就像在 group by 子句中重复计算列一样。
Your sample query does not reveal at all what you want to do, are you really updating allrecords to the same value (fixed text), as well as allcolumns per record. If you updated to make the question more relevant, there is probably a better answer.
您的示例查询根本没有显示您想要做什么，您是否真的将所有记录更新为相同的值（固定文本），以及每条记录的所有列。如果您更新以使问题更相关，则可能有更好的答案。
但是现在，对于您的特定查询，您可以使用这样的东西
UPDATE customer
SET forenames=ot.forenames,
surname = fixedText,
middlename = fixedText
FROM (select o.*, CASE
WHEN safeplace IS NULL
THEN 'test2 SAFEPLACE IS NULL'
ELSE 'test2 Safeplace IS NOT NULL'
END fixedText
from order_transaction o) ot
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
AND customer.custid = 1234
回答by Dumitrescu Bogdan
If you need to copy the exact case more times (alot more then 2) you can use the next query. But you have to really need to copy the case, not with test and test2 (that is not exactly the same case). Evidently if you need to concatenate text like test / test 2 or whatever to a result then you can do it in the select statement. ex: surname = 'test '+st.result so there are some possibilities to do some 'hacks'.
如果您需要多次复制确切的大小写（多于 2 次），您可以使用下一个查询。但是您必须真的需要复制案例，而不是使用 test 和 test2 （这不是完全相同的案例）。显然，如果您需要将 test / test 2 等文本或其他内容连接到结果中，那么您可以在 select 语句中进行。例如： surname = 'test '+st.result 所以有一些可能性可以做一些 'hacks'。
UPDATE customer
SET forenames=ot.forenames,
surname = st.result,
middlename = st.result
FROM order_transaction ot
JOIN (select 1 as ID,'test SAFEPLACE IS NULL' as result
union
select 2,'test SAFEPLACE IS NULL') st on case when ot.safeplace is null then 1 else 2 end = st.id
WHERE customer.custid = ot.custid
AND ot.trans_orderid = 5678
AND customer.custid = 1234
