please tell me how to create update query for such a problem

Question

i have two tables:

1.
CREATE TABLE [dbo].[HotelSourceMap](
[hotelsourcemapid] [bigint] IDENTITY(1,1) NOT NULL,
[dspartnerid] [bigint] NOT NULL,
[dshotelid] [bigint] NOT NULL,  
[countrycode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[countryid] [bigint] NULL)

2.
CREATE TABLE [dbo].[country](
[countryId] [smallint] IDENTITY(1,1) NOT NULL,
[countryName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[countryCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

situation is like this: i want to update countryid in HotelSourceMap table from country.countryid . where hotelsourcemap.countrycode = country.countrycode

some thing like this:

UPDATE HotelSourceMap
SET
HotelSourceMap.countryid =country.countryId  
WHERE
HotelSourceMap.countrycode = country.countryCode

Answer 1

There's probably a set-based solution for this, in which case that would be preferable, and I'm sure someone will post it, but until then, at least this will do the job:

UPDATE
    HotelSourceMap
SET
    countryid = (SELECT countryId FROM country WHERE country.countryCode = HotelSourceMap.countrycode)

Answer 2

This is really questionable table design, but here is the SQL:

UPDATE    HotelSourceMap
SET       countryid = co.countryId
FROM      HotelSourceMap AS hsm
    JOIN  country        AS co
        ON (hsm.countryCode = co.countryCode)