check for designation and assign new designation else retain the same

Question

i have tables that has employee data and there designations , we are moving few deisgnated employees to diff roles . below are the coindiotns . for all others the roles remain the same

Answer 1

Try this:

DROP TABLE IF EXISTS #temp;
CREATE table #temp ( employee varchar(100), designation varchar(100))


  insert into #temp values ('AAA','IT  Sales Person'),('AAA','Dept head'),('AAA','supervisor')
  insert into #temp values ('BBBB','Sales Manager - Area'),('BBBB','Dept head')
  insert into #temp values ('CCCC','Sales Manager - Region') , ('CCCC','Dept head')

  SELECT employee, designation, CASE designation WHEN 'IT Sales Person' THEN 'Salesperson'
  WHEN 'Sales Manager - Area' THEN 'Sales Manager' WHEN 'Sales Manager - Region' THEN 'Salesperson' ELSE designation END AS [New Designation]
  FROM #temp

Answer 2

Do you have a lookup table to hold these designation values? (you need to update only one place).
If you don't, you can update these values with your new rules use a CASE expression for column values update:

Update yourtable
Set col = Case
when col='IT Sales Person ' then 'SalesPerson'
when col='Sales Manager --Area' then ' Sales Manager'
when col='Sales Manager --Region' then ' SalesPerson'
end
Where col in ('IT Sales Person ','Sales Manager --Area','Sales Manager --Region');

Please provide your sample DDL and data if need more help.