DECLARE @DefaultMISGuestType INT
DECLARE @DefaultMISPurposeCode INT 
DECLARE @HotelCountryNumber INT

SELECT @DefaultMISGuestType = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'DefaultMISGuestType'
SET @DefaultMISGuestType = ISNULL(@DefaultMISGuestType, - 1)

SELECT @DefaultMISPurposeCode = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'DefaultMISPurposeCode'
SET @DefaultMISPurposeCode = ISNULL(@DefaultMISPurposeCode, - 1)

SELECT @HotelCountryNumber = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'CountryCode'
IF @@ROWCOUNT = 0
  SELECT TOP 1 @HotelCountryNumber = CountryNumber FROM Countries WITH (NOLOCK)

UPDATE MIS
SET 
  MIS.FK_GuestTypeNumber = CASE WHEN MIS.FK_GuestTypeNumber IS NULL THEN ISNULL(Reservations.GuestType, @DefaultMISGuestType) ELSE MIS.FK_GuestTypeNumber END,
  MIS.FK_PurposeCodeNumber = CASE WHEN MIS.FK_PurposeCodeNumber IS NULL THEN ISNULL(Reservations.PurposeCode, @DefaultMISPurposeCode) ELSE MIS.FK_PurposeCodeNumber END,
  MIS.FK_CountryNumber = CASE WHEN MIS.FK_CountryNumber IS NULL THEN 
    ISNULL(CASE WHEN CASE WHEN ReservationAddress.CountryNumber IS NULL THEN raf.CountryNumber ELSE ReservationAddress.CountryNumber END IS NULL THEN (SELECT TOP 1 CountryNumber FROM Countries WITH (NOLOCK) WHERE Countries.CountryCode = ReservationAddress.CountryCode) ELSE CASE WHEN ReservationAddress.CountryNumber IS NULL THEN raf.CountryNumber ELSE ReservationAddress.CountryNumber END END, @HotelCountryNumber)
  ELSE MIS.FK_CountryNumber END
FROM MISTable MIS
LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = MIS.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber
  AND ReservationAddressXTab.AddressTypeNumber = 1
LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MIS.FolioNumber
LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MIS.ReferenceNumber
  AND Reservations.GuestNumber = MIS.GuestNumber
WHERE MIS.FK_GuestTypeNumber IS NULL OR MIS.FK_PurposeCodeNumber IS NULL OR MIS.FK_CountryNumber IS NULL