Case when doesn't accept BEGIN

Question

Case doesn't accept Begin

I need to update 2 separate fields in one case, something like below

UPDATE PYR SET
    MSR = 
      Case 
        When MSR = :WRKH then
        Begin
          MSR = NULL
          WRKDAY = WRKDAY -1
        end;
      end;

`UPDATE PYR SET MSR = NULL, WRKDAY = WRKDAY -1 WHERE MSR = :WRKH` — Iłya Bursov, Apr 06 '23 at 04:09
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Apr 06 '23 at 22:40

Mark Rotteveel · Answer 1 · 2023-04-06T08:19:23.473

This is invalid syntax, and will not work. You seem to think a CASE is like an IF in PSQL (Firebird's procedural language), but it is not. It is also not how the UPDATE syntax works. CASE is a conditional value expression, and it can only return one value.

The correct update statement would be putting the condition in the WHERE clause (as also suggested by Iłya Bursov in the comments):

update PYR set MSR = NULL, WRKDAY = WRKDAY -1
where MSR = :WRKH

If this is part of a more complex statement that also updates other fields, you need to write the conditions per field:

update PYR set 
  MSR = case when MSR = :WRKH then null else MSR end, 
  WRKDAY = case when MSR = :WRKH then WRKDAY - 1 else WRKDAY end

or (using NULLIF to simplify)

update PYR set 
  MSR = nullif(MSR, :WRKH), 
  WRKDAY = case when MSR = :WRKH then WRKDAY - 1 else WRKDAY end

Case when doesn't accept BEGIN

1 Answers1