SQL-使用CASE语句更新，是否需要多次重复同一CASE？ [英] SQL - Update with a CASE statement, do I need to repeat the same CASE multiple times?

问题描述

我的UPDATE语句如下：

My UPDATE statement goes along the lines of:

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

以上方法有效。它基本上检查另一个表中的字段是否为NULL，然后相应地更新客户的姓和中间名。如您在上面看到的，我已经重复了两次相同的CASE语句。我的问题是-有一种方法可以只指定一次CASE语句吗？

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?

要点是，如果我说要根据特定条件更新10个字段，我需要包括10个类似的CASE条件吗？还是可以将SQL改进为仅包含一个CASE，并在WHEN / ELSE子句中进行10个字段更新？

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?

（我使用的是Postgresql 8.2数据库，但我相信

(I'm using a Postgresql 8.2 database but I believe the above is standard SQL).

非常感谢，
Rishi

Many thanks, Rishi

推荐答案

我相信上面是标准的SQL

I believe the above is standard SQL

实际上不是。标准SQL没有 UPDATE..FROM 语法。相反，您需要为每个 SET 子句使用一个标量子查询，再为 EXISTS 的子句使用一个标量子查询，因此Standard语法是偶数更重复的，例如

Actually, it isn't. Standard SQL does not have a UPDATE..FROM syntax. Rather, you need to use a scalar subquery for each SET clause plus another for EXISTS, so the Standard syntax is even more repetitive e.g.

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
                  );

虽然语法看起来是重复的，但是好的优化程序应该能够识别重复并相应地进行优化。当然，当前版本的SQL产品是否真的在优化此产品方面做得很好，这是另一回事。但是请考虑一下：如果您选择的SQL产品支持标准语法，但实际上并未对其进行适当的优化，那么支持是否值得？

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（确实应该使用IMO :），并且希望使用更紧凑的语法，请查看 MERGE 或合并（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 MERGE or MERGE (SQL) e.g. could look more like this:

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;

这篇关于SQL-使用CASE语句更新，是否需要多次重复同一CASE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！