Do I need a where clause in a conditional UPDATE?

We imported a lot of data from another table. Now I’m trying to correct some of them.

UPDATE [x10ddata].[dbo].[ResourceTest]
  SET [Country] = (CASE
      WHEN [Country] IN ('Aezerbaijan', 'AZERBIJAN') THEN 'Azerbaijan'
      WHEN [Country] = 'Belgique'    THEN 'Belgium'
      WHEN [Country] = 'China (RPC)' THEN 'China'
      WHEN [Country] = 'Columbia'    THEN 'Colombia'
     WHEN [Country] = 'Croatia (Local Name: Hrvatska)' THEN 'Croatia'
     .....//...
     WHEN [Country] IN ('U.S.', 'U.S.A', 'U.S.A.', 'US', 'USA', 
                         'USA - Maryland', 'USAQ') THEN 'United States'
    END)   
  GO

I didn’t use ELSE because many rows have valid country. My question is to know whether I need to the WHERE clause to filter the rows that will be affected?

The reason I’m asking this question is that, I’ve selected into a test table and tried the the script. According to the output, all the rows affected, but when I check closely, not all the rows were affected. It’s confusing.

Thanks for helping

Answer

The case statement will return null if none of the when clauses are met. You can verify this with this simple sql:

declare @i int
set @i = 2
select case when @i = 1 then 'A' end AS Column1

This will return null since @i is not 1.

To fix this in your case, you can either add the where clause like you said, or the simpler option might be to add ELSE [Country] after all of your WHEN clauses. This would mean “If I don’t need to change the country field, then just use the same value that was there before.”