data translating issues I have these two table here in ssms:


one is called client table, which have a column called city, and it is really city name, like new york, etc.
Another table is called city table, it has city's ID and city name. such as CityID:1 City: New york. 


So in my Client table, I know how to create another column called CityID, but how can I populate the CityID column in the client table, entering them by searching the CityID in the City table?
You have to use the following statement:

UPDATE A 
SET A.CityID = B.CityID 
FROM dbo.client AS A
INNER JOIN dbo.city AS B 
       ON A.city = B.City;


If you don't want a [case/blank spaces] sensitive update (' New York   ' = 'NEW YORK') use this statement:

UPDATE A 
SET A.CityID = B.CityID 
FROM dbo.client AS A
INNER JOIN dbo.city AS B 
       ON rtrim(ltrim(upper(A.city))) = rtrim(ltrim(upper(B.City)));