How to convert the select statement into an update statement

1

SELECT * FROM EMP_MTN WHERE PATINDEX('%[]N',EmpID) = 0 AND PATINDEX('%[]N',[EmpName]) = 0
Except
select * from EMP_MTN
where EmpID like '51%' OR EmpID like '85%' OR EmpID like 'TR%' OR EmpID like 'MY%' OR EmpID like 'CR%' or EmpID like 'JP%' or EmpID like '80%'

2

You don't give enough information. Update what column into what value?

3

Good day sir,

Sorry for the lack of information. Actually the below is my situation that I need help with.

  1. Convert all old data (without _N) to _OLD. Example, 000015 to 000015_OLD, same goes for the EmpName table, FURUSAKI to FURUSAKI_OLD

while excluding / ignoring certain character such as '85XXXX', '80XXXX', 'TRXXXX', 'CRXXXX', 'MY', etc

Basically, I wan amend the data with '00XXX' in front only.

  1. Convert all new data (with _N) to without '_N'. Example, 000001_N to 000001
4 
 if object_id ('tempdb..#EMP_MTN') is not null drop table #EMP_MTN


 create table #EMP_MTN (EmpID varchar(255), [EmpName] varchar(255))

 insert into #EMP_MTN values 
('801124','Zukri bin Sulaiman_N'),
('JP0015','Masaru furusaki')


select * from #EMP_MTN



Update #EMP_MTN
set 
 EmpID = EmpID+'_OLD'
,[EmpName] = [EmpName]+'_OLD'
where right([EmpName],2) <> '_N'


select * from #EMP_MTN


Update #EMP_MTN
set 
[EmpName] = substring([EmpName],0,charindex('_N',[EmpName]))
where right([EmpName],2) = '_N'

select * from #EMP_MTN