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

ahmed salah 3,216 Reputation points
2021-09-18T12:36:13.78+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,074 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-09-20T01:56:58.553+00:00

    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"

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 113.8K Reputation points
    2021-09-18T19:51:48.323+00:00

    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
    
    0 comments No comments