In the query below, if both conditions are true then the last entry is updated in ErrorMessage. If one record has country more than 3 characters and length of HR_DOMAIN_Code is not equal to 3, then ErrorMessage should look like 'Invalid Country' & 'Invalid HR_DOMAIN_CODE':
update A
set ErrorMessage =
(
case when len(Country) > 3 then 'Invalid Country'
when len(HR_DOMAIN_CODE) != 3 then 'Invalid HR_DOMAIN_CODE'
else NULL
end)
You can just add another WHEN
to handle both conditions:
UPDATE A
SET ErrorMessage =
CASE
WHEN LEN(Country) > 3 AND LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid Country & Invalid HR_DOMAIN_CODE'
WHEN LEN(Country) > 3 THEN 'Invalid Country'
WHEN LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid HR_DOMAIN_CODE'
ELSE NULL
END
However, the above will be very complicated once you have multiple combinations to check. My suggestion is to use a separate CASE
expression of each of the condition. Something like this:
UPDATE A
SET Errormessage = STUFF((
SELECT
CASE WHEN LEN(Country) > 3 THEN '& Invalid Country' ELSE '' END +
CASE WHEN LEN(HR_DOMAIN_CODE) != 3 THEN '& Invalid HR_DOMAIN_CODE' ELSE '' END
), 1, 2, '')