Post reply

if record exist update partlevel from 1 to 0 ?

  • April 10, 2020 at 10:35 pm

    #3742131

    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

  • April 10, 2020 at 11:39 pm

    #3742137

    So far so good. Could you post the expected output of the query you need?

  • April 10, 2020 at 11:49 pm

    #3742139

    OK

    TradeCodeIdPartIdPartlevelCodeTypeCodeValue
    1         1222  1         ECCS-USAB123-US
    2         1255  1         ECCS-USAB555-US
    3         1444  1         ECCS-USAB666-US
    4         1931  1         ECCS-USAB756-US
    5         1222  0         ECCS-URBAB123-URB
    6         1255  0         ECCS-URBAB555-URB
    7         1444  0         ECCS-URBAB666-URB
    8         1931  0         ECCS-URBAB778-URB

Viewing 3 posts - 1 through 2 (of 2 total)

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