Clever Geek Handbook

SQL Case Statement in set; Does it always work?

I was wondering if in the following statement:

  UPDATE u 
  SET u.isactive = 
        (
        CASE WHEN e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1 THEN 
              0
        WHEN e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1 THEN 
              1 END
         ),
      u.UpdatedB y= 0 
  FROM tbl_e e                                        
       INNER JOIN tbl_Users u ON e.id=u.id

If the conditions in the case statement are not met, for example, u.IsActive = 1 and e.LVStatus = 'B', will u.UpdatedBy = 0 still be asked? I was hoping that if the conditions in the case statement are not met, do nothing, perhaps if I need this behavior, I just need to separate the update statements with different clauses. Thank!

+3


source to share


4 answers


Since your query sets u.UpdatedBy

to zero unconditionally, the answer is yes, the value u.UpdatedBy

will be set regardless of the result of the expression CASE

through which it is set u.isactive

.

To overcome this, you can add a separate expression CASE

for the field u.UpdatedBy

and use the current value if the conditions of the other statement are CASE

not met:

UPDATE u 
SET u.isactive = CASE
        WHEN e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1 THEN 0
        WHEN e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1 THEN 1
        ELSE u.isactive
    END
,   u.UpdatedBy= CASE
        WHEN (e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1) OR
             (e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1)
        THEN 0
        ELSE u.UpdatedBy
    END 
FROM tbl_e e                                        
     INNER JOIN tbl_Users u ON e.id=u.id


Alternatively, you can move this condition into a clause WHERE

, for example:

UPDATE u 
SET u.isactive = CASE
        WHEN e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1 THEN 0
        WHEN e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1 THEN 1
        -- No ELSE is needed, because WHERE filters out all other cases
    END
,   u.UpdatedBy=0
FROM tbl_e e
INNER JOIN tbl_Users u ON e.id=u.id
WHERE (e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1) OR
      (e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1)
+2


source


Both installation conditions will always work. A CASE

non-clause expression ELSE

has an implicit one ELSE NULL

. See here .



If it u.isactive

has a restriction NOT NULL

, you will receive a restriction violation and nothing will be updated by the operator.

+1


source


Just do it SET u.isactive = (CASE... ELSE u.isactive end)

.

Thus, if your criteria in the status of the case are not met, just leave it as the present value. You can probably leave without making any statements, but I don't like that.

0


source


One way to do this is to add a clause else

and return the original column value:

UPDATE u 
  SET u.isactive=(CASE WHEN e.LVStatus<>'B' AND u.IsActive=1 AND u.AutoUpdate=1 THEN 0
                       WHEN e.LVStatus='B' AND u.IsActive=0 AND u.AutoUpdate=1 THEN 1 
                       ELSE u.isactive END),
      u.UpdatedBy=0 
  FROM tbl_e e                                        
       INNER JOIN tbl_Users u ON e.id=u.id
0


source






More articles: