update final code where not contain x then null if not contain x and another cod

  • 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
  • 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

  • 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

  • 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
  • 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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply