SQLTeam.com | Weblogs | Forums

If record exist update partlevel from 1 to 0?


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


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

 TradeCodeId int identity(1,1),
 PartId  int,
 Partlevel int,
 CodeType  nvarchar(50),
 CodeValue nvarchar(50)
 insert into #TradeCode(PartId,Partlevel,CodeType,CodeValue)VALUES

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

Expected Result

TradeCodeId PartId Partlevel CodeType CodeValue
1 1222 1 ECCS-US AB123-US
2 1255 1 ECCS-US AB555-US
3 1444 1 ECCS-US AB666-US
4 1931 1 ECCS-US AB756-US
5 1222 0 ECCS-URB AB123-URB
6 1255 0 ECCS-URB AB555-URB
7 1444 0 ECCS-URB AB666-URB
8 1931 0 ECCS-URB AB778-URB

hope this helps :slight_smile:

Merge Statement !!!

what merge does .. !!
if exists update .. if not exists insert !!!