How to add mapped to in table trade codes where mapped from exist ?

I work on SQL server 2012 I face issue I can't insert mapped to into table trade codes where mapped from exist
on table trade code

so firstly I will get MapCodeTypeFrom and MapCodeValueFrom from table settings

second i will search on table tradecodes for MapCodeTypeFrom and MapCodeValueFrom

if MapCodeTypeFrom and MapCodeValueFrom exist on table trade Codes

then add MapCodeTypeTo and MapCodeValueTo for every part

that meaning every part have MapCodeTypeFrom and MapCodeValueFrom on table TradeCodes
must have MapCodeTypeTo and MapCodeValueTo

with another meaning every part must have two rows
first row for MapCodeTypeFrom and MapCodeValueFrom
second row for MapCodeTypeTo and MapCodeValueTo

create table #settings
(
MapId int,
MapCodeTypeFrom nvarchar(50),
MapCodeValueFrom int,
MapCodeTypeTo nvarchar(50),
MapCodeValueTo int
)
insert into #settings(MapId,MapCodeTypeFrom,MapCodeValueFrom,MapCodeTypeTo,MapCodeValueTo)
values
(1222,'ECCN',9910,'HTS',9920),
(1222,'Hom',9950,'VOM',9960)
--DROP TABLE #TradeCodes
create table #TradeCodes
(
PartId int,
CodeType nvarchar(50),
Code int,
)
insert into #TradeCodes(PartId,CodeType,Code)
values
(1334,'ECCN',9910),
(1971,'ECCN',9910),
(2050,'ECCN',9910),
(3000,'VOM',9950),
(3600,'VOM',9950),
(3700,'VOM',9950)

final rows must added on table trade codes that represent mapped to is :

mapped to will added in case of mapped from code type and value Exist on table trade codes

Hello,

I am a bit confused about your value 'Hom' in settings table.

There are couple of possibilities. First, may be it should be 'VOM'. Second, the value for last 3 rows in tradecodes table should be 'Hom'.

Assuming one of the two scenarios mentioned above, the following should be able to solve the problem.

declare @settings table
(
MapId int,
MapCodeTypeFrom nvarchar(50),
MapCodeValueFrom int,
MapCodeTypeTo nvarchar(50),
MapCodeValueTo int
)
insert into @settings (MapId,MapCodeTypeFrom,MapCodeValueFrom,MapCodeTypeTo,MapCodeValueTo)
values
(1222,'ECCN',9910,'HTS',9920),
(1222,'VOM',9950,'VOM',9960)
declare @TradeCodes table
(
PartId int,
CodeType nvarchar(50),
Code int
)
insert into @TradeCodes (PartId, CodeType, Code)
values
(1334,'ECCN',9910),
(1971,'ECCN',9910),
(2050,'ECCN',9910),
(3000,'VOM',9950),
(3600,'VOM',9950),
(3700,'VOM',9950)
update tc
set tc.[CodeType] = s.[MapCodeTypeTo], tc.[Code] = s.[MapCodeValueTo]
from @TradeCodes tc
inner join @settings s on tc.[CodeType] = s.[MapCodeTypeFrom] and tc.[Code] = s.[MapCodeValueFrom]
select *
from @TradeCodes

Please review.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.