Conditional update

Transact-SQL
, ,
1

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,'ddx'),
 (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
2

You could try something like:
UPDATE r
SET r.Finalcode = CASE WHEN p.PriorityName like ('%x%') THEN NULL ELSE p.priorityid END
from #Replacment r
inner join #priority p on p.priorityCode=r.ReplacmentCode

This is not tested and may not be the best way, but I hope it gives you an idea.

3

exactly below is what i need

1- when prioritycode=replacementcode

if priorityname contain x and no other codes then UPDATE FinalCode By NULL

AS replacement 199 and 700 and 600

2- when prioritycode=replacementcode

if priorityname not contain x then UPDATE FinalCode By priorityid not contain x

as replaceent 500

so replacement code 190 have two codes i take one that not have x

so f code not have x then i will get it

and this is expected result

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