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

28,917

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

Solution 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

28,917

rishijd

Updated on January 04, 2020

Comments

rishijd over 4 years
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
Dumitrescu Bogdan about 13 years

Not really the best solution Richard. As your select first scans the table and adds the result and after that filters it.
RichardTheKiwi about 13 years

@Dumitrescu Bogdan / I don't think it does that. Because PostgreSQL will unroll the query to apply the filters into the subquery, but still retain the ability to use the defined (computed) column
onedaywhen about 13 years

The OP's syntax is not Standard SQL (also note that the SQL Standard is international and the 'A' in 'ANSI' stands for 'American' so "ISO-compliant SQL" would be more appropriate here :)
Dumitrescu Bogdan about 13 years

This for sure would be the logical way it should handle it .. though I never relied on it. I do not have unfortunately a psql server available to be able to actually see this behavior.
RichardTheKiwi about 13 years

@Dumitrescu Bogdan / I do, and I have tested it. Explain clearly shows it working as it should.
onedaywhen about 13 years

@Richard aka cyberkiwi: "Can you please elaborate on which part isn't standard?" -- the UPDATE statement the OP used is not Standard SQL. How can I put it another way? It is structured in a way that is not valid syntax? For example, the Standard UPDATE does not have a FROM clause, one can only refer to a value from another table in the SET clause using a scalar subquery, which doesn't leave much that is valid syntax.
onedaywhen about 13 years

@Richard aka cyberkiwi: "Find me some major sites/papers" -- go to Wikipedia, search for "SQL-92", go to the two site/papers referenced (the spec in plaintext and the BNF respectively) which I believe are the two major references for the SQL-92 Standard. Note for both are called "ISO/IEC" and do not use "ANSI".
onedaywhen about 13 years

You said, "Find me some major sites/papers" and I think I did. I wasn't looking for an argument. If the actual text of the Standard is not proof enough for you then I can prove nothing. Please note my original comment was in parentheses (i.e. no biggie) and included a smiley :)
onedaywhen about 13 years

"no biggie" means "no big deal"
onedaywhen about 13 years

:) means "please don't take this too seriously"
onedaywhen about 13 years

I see you've removed your earlier misstatement ("that is what you need to do using ANSI/ISO-compliant SQL") so I've removed my downvote :)
onedaywhen about 13 years

@Richard aka cyberkiwi: updated answer to add a MERGE example to show it is more compact while remaining Standard SQL.