Geek asks and answers

SQL UPDATE with CASE expression gives incomplete results

The following code is intended to update individual fields only if there is a value related to this field.

Returns an incomplete set of values. Millions of lines are true, but several thousand lines have values ​​that are incorrectly set to NULL.

Is this a SQL limitation, or am I missing something?

UPDATE a
    SET ResultType1 = CASE WHEN b.[Type] = 'type1' THEN b.value END
       ,ResultType2 = CASE WHEN b.[Type] = 'type2' THEN b.value END
    FROM tableA AS a
    INNER JOIN tableB AS b ON a.ID = b.ID
+3
source share
1 answer

update certain fields only when there is a value related to that field

makes me think you really want to do this:

UPDATE a
  SET ResultType1 = CASE WHEN b.[Type] = 'type1' 
                           THEN b.value 
                         ELSE ResultType1 
                    END
    , ResultType2 = CASE WHEN b.[Type] = 'type2' 
                           THEN b.value 
                         ELSE ResultType2 
                    END
FROM tableA AS a
    INNER JOIN tableB AS b ON a.ID = b.ID

So, ResultType1 / 2 will be set to their existing values, if the b.Type conditions are not met, INSTEAD is NULL.

, " ", , NULL.

, , - ELSE NULL CASE.

ResultType1 ResultType2 , , 'type', :

UPDATE a
  SET ResultType1 = CASE WHEN b.[Type] = 'type1' 
                           THEN b.value 
                         ELSE NULL                 --- this line can be removed
                    END
    , ResultType2 = CASE WHEN b.[Type] = 'type2' 
                           THEN b.value 
                         ELSE NULL                 --- this one, too
                    END
FROM tableA AS a
    INNER JOIN tableB AS b ON a.ID = b.ID
WHERE b.[Type] IN ('type1', 'type2')
+6

More articles:


All Articles