locked
if record exist update partlevel from 1 to 0 ? RRS feed

  • Question

  • User696604810 posted

    problem

    if record exist on trade code table update partlevel from 1 to 0 ?

    the code below insert new record on trade code for codetypeto and codevalueto if not exist on table tradecode

    i need to modify code below if  codevalueto and code typeto both exist on table tradecode

    then update partlevel from 1 to 0 to codetypeto and codevalueto on tradecode table that already exist

    else 

    insert record on tradecode table to  codetypeto and codevalueto that not exist

    create table #MappingCodeValue
     (
     id int identity (1,1),
      CodeTypeFrom  nvarchar(50),
     CodeValueFrom  nvarchar(50),
     CodeTypeTo  nvarchar(50),
     CodeValueTo  nvarchar(50)
     )
     INSERT INTO #MappingCodeValue
     (CodeTypeFrom,CodeValueFrom,CodeTypeTo,CodeValueTo)
     VALUES
     ('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
     ('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
     ('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),
     ('ECCS-US','AB756-US','ECCS-URB','AB778-URB')
    
    
     CREATE TABLE #TradeCode
     (
     TradeCodeId int identity(1,1),
     PartId  int,
     Partlevel int,
     CodeType  nvarchar(50),
     CodeValue nvarchar(50)
     )
     insert into #TradeCode(PartId,Partlevel,CodeType,CodeValue)VALUES
     (1222,1,'ECCS-US','AB123-US'),
     (1255,1,'ECCS-US','AB555-US'),
     (1444,1,'ECCS-US','AB666-US'),
     (1931,1,'ECCS-US','AB756-US')
    
    insert into #TradeCode
    select c.PartId, c.Partlevel, c.CodeType, m.CodeValueTo
    from #MappingCodeValue as m
    inner join #TradeCode as c on c.CodeType = m.CodeTypeFrom and c.CodeValue = m.CodeValueFrom
    where not exists( select * from #TradeCode where CodeType = c.CodeType and CodeValue = m.CodeValueTo)
    Select * from #TradeCode

    Friday, April 10, 2020 10:16 PM

Answers

  • User452040443 posted

    Try:

    merge #TradeCode as t
    using 
    (
        select c.PartId, c.Partlevel, c.CodeType, m.CodeValueTo
        from #MappingCodeValue as m
        inner join #TradeCode as c 
            on c.CodeType = m.CodeTypeFrom and c.CodeValue = m.CodeValueFrom
    ) as u on u.CodeType = t.CodeType and u.CodeValueTo = t.CodeValue
    when matched then
        update set PartLevel = 0
    when not matched then
        insert (PartId, Partlevel, CodeType, CodeValue)
        values (u.PartId, u.Partlevel, u.CodeType, u.CodeValueTo);

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 11, 2020 2:50 PM