update final code where not contain x then null if not contain x and another code exist then take it

Question

i work on SQL server 2012 I face issue i can't update table replacement field name
final code where replacement code=priority code from table priority
where priority name not contain x

create table #priority
(
priorityid int,
priorityCode nvarchar(20),
priorityname nvarchar(100)
)
insert into #priority(priorityid,priorityCode,priorityname)
values
(12,120,'ppx'),
(17,190,'ppX'),
(22,190,'ylm'),
(32,810,'dmj'),
(42,860,'ddy'),
(55,900,'xyz')

create table #Replacment
(
Replacment int,
ReplacmentCode nvarchar(20),
finalcode nvarchar(100)
)
insert into #Replacment(Replacment,ReplacmentCode,finalcode)
values
(199,120,NULL),
(500,190,NULL),
(510,810,NULL),
(600,860,NULL),
(700,900,NULL)

what I try

update  r set r.finalcode=p.priorityid from #Replacment r
inner join  #priority p on p.priorityCode=r.ReplacmentCode

I need to change update final code with priority ID where priority name have character x then search another priority id if it exist then take it if not exist then
assign final code to NULL

AS EXAMPLE ABOVE
120=120 then there are another priority name not have x
no exist then NULL
190=190 THEN there are another priority name have x
exist then take it 22

so how to update final code where priority name have x and no another id matched then null if another one and not contain x then update it

expected result to table replacement after update column final code

Replacment ReplacmentCode finalcode
199 120 NULL
500 190 22
510 810 32
600 860 42
700 900 NULL

Answer 1

Hi @ahmed salah ，

Please try:

;WITH cte  
as(SELECT *,ROW_NUMBER() OVER(PARTITION BY priorityCode  
ORDER BY priorityCode)rr  
FROM #priority)  
  
UPDATE r  
SET finalcode=priorityid  
FROM #Replacment r  
INNER JOIN cte c ON r.ReplacmentCode=c.priorityCode  
WHERE c.priorityname NOT LIKE '%x%'  
OR c.rr>1   

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer"

Answer 2

Check a possibility:

update r
set finalcode = 
    (select top(1) priorityId 
    from #priority 
    where priorityCode = r.ReplacmentCode and priorityname not like '%x%'
    order by priorityid)
from #Replacment r