"Cannot insert the value NULL into column" when trying to update a column in SQL

I am trying to update values of a column which is not null using the query below

update Bom set CountryCode=
case
when CurrencyId='CHF' then 'CH' 
when CurrencyId='NZD' then 'NZ'
when CurrencyId='KRW' then 'KR'
when CurrencyId='HKD' then 'HK'
when CurrencyId='MXN' then 'MX' 
when CurrencyId='USDe' then 'DE'
when CurrencyId='JPY' then 'JP'
when CurrencyId='EUR' then 'DE'
when CurrencyId='DKK' then 'DK' 
when CurrencyId='GBP' then 'GB'
when CurrencyId='AUD' then 'AU'
when CurrencyId='RMB' then 'CN'
when CurrencyId='USDu' then 'US' 
when CurrencyId='NOK' then 'NO'
when CurrencyId='CAD' then 'CA'
when CurrencyId='USDm' then 'MX'
when CurrencyId='SEK' then 'SE'
when CurrencyId='SGD' then 'SG'
when CurrencyId='TWD' then 'TW'
when CurrencyId='ZAR' then 'ZA'
end

but for some reason I am getting an error saying

Cannot insert the value NULL into column 'CountryCode', table 'Mouser_BOM.dbo.Bom'; column does not allow nulls. UPDATE fails.

I am not inserting any null values but stil开发者_JAVA技巧l getting this error. Can someone help why I am getting this.

Add an ELSE clause to the end of your statement to catch the case that none of your conditions match. That should get rid of the error. So something like:

...
  when CurrencyId='SGD' then 'SG'
  when CurrencyId='TWD' then 'TW'
  when CurrencyId='ZAR' then 'ZA'
  else '??'
end

That's a rather nasty case statement there. My guess is that you're not matching one of your cases.

Looking at it, isn't it easier to achieve the same thing in a different way?

With a few exceptions, pretty much all of your updates could be done with a substring.

e.g.

UPDATE
  BOM
SET 
  CountryCode = SUBSTRING(CountryCode,1,2)
WHERE
  CountryCode IN
(
  'CHF',
  'HKD'
  -- the rest of these go here
)

Then do the exceptions manually.

While it's cool that you've got an answer to this specific problem, my advice is to spot the patterns and not make life too hard on yourself.

First, your case statement can be simplified.

 CASE CurrencyId 
 WHEN 'CHF' THEN 'CH'
 WHEN 'NZD' THEN 'NZ' 
 WHEN 'KRW' THEN 'KR' 
 END

Second, there must be a condition that doesn't match any of them. Do a select to find it:

        SELECT *
        FROM Bom
        WHERE case CurrencyId 
        when 'CHF' then 'CH' 
        when 'NZD' then 'NZ' 
        when 'KRW' then 'KR' 
        ...
        end IS NULL