SQL - Update with a CASE statement, do I need to repeat the same CASE multiple times?

Question

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

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?

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?

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

Many thanks, Rishi

Answer 1

I believe the above is standard SQL

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

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?

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;

Answer 2

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.

Your sample query does not reveal at all what you want to do, are you really updating all records to the same value (fixed text), as well as all columns per record. If you updated to make the question more relevant, there is probably a better answer.

---

But for now, for your specific query, you can use something like this

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