September 18, 2021 at 12:40 pm
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
Replacment ReplacmentCode finalcode
199 120 NULL
500 190 22
510 810 32
600 860 42
700 900 NULL
September 19, 2021 at 6:06 am
I'm sorry, but I think that I'm misunderstanding something. In your results you updated the record that has replacementCode 860, but according to my understanding from your explanation, it should have null value in ReplacementCode column because the replacementName has the letter x in it. If you have just to select replacmentCode that their names don't contain the letter x, you can just add to the update statement a where clause the excludes those records (where charindex('x', p.priorityname) = 0) . If you need something else, then pleas explain the logic that should be applied. Take into consideration, that you also didn't explain the logic that should be applied in case that you have more then one name without the letter x to the same replacementCode.
Adi
September 20, 2021 at 1:59 am
For ReplacmentCode=120 you have finalcode = NULL
For ReplacmentCode=860 you have finalcode = 42
Can you explain the difference between these 2 use cases?
_____________
Code for TallyGenerator
September 29, 2021 at 6:03 pm
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
September 30, 2021 at 12:16 pm
Maybe you could use a CTE to make sure the right table source of the JOIN in the UPDATE statement contains appropriate unique priorityCode's
with unq_priority_cte(priorityid,priorityCode,priorityname) as (
select top 1 with ties *
from #priority
where priorityname not like '%x%'
order by row_number() over (partition by priorityCode order by (select null)))
update r
set r.finalcode=p.priorityid
from #Replacment r
join unq_priority_cte p on p.priorityCode=r.ReplacmentCode;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply