question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft commented

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

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-generalsql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

my issue is update with case when so any one can help me

update r set r.finalcode=case when p.priorityname like '%x%' then p.priorityid end from #Replacment r
inner join #priority p on p.priorityCode=r.ReplacmentCode

0 Votes 0 ·

Why you get 42 and not null in this row: 600 860 42

Please continue the explanation of the rest of the rows in the expected result SET

0 Votes 0 ·

yes you are right
you are correct it must be null
instead i modified it from ddx to ddy
now it must take 42 because it not have x and the value ddy
but before i modify it will take null because ddx only value have x
so it will take null
so any one can help me

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ahmedsalah-1628

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"

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.