How to update status with conflict data where chemical temp

problem

How to update status with conflict data where chemical temp table have same chemical Id on temp table #temp ?

steps to achieve that

1- get related parts to part exist on temp table #temp that have same masked id from temp table #parts

in this case result will be

PartId MaskId ChemicalId
200 1000 901
500 1700 909
600 1700 909

2- check on temp table #chemical for partid or related part id for same maskId

if chemicalid on step 1 different to chemicalid on temp table chemical

then nothing happen on status .

if checmicalid on step 1 same chemicalid on temp table chemical

then status will updated to conflict based on part id .

Here 200 have 901 chemical id on temp table #temp and on chemical temp table have 901 for 100

then status will be conflict because it related to same mask id and have same chemical id 901 .

Here 700 have 909 chemical id on temp table #temp and on chemical temp table have 909 for 500 and 600

then status will be nothing changed because it related to same mask id and have different chemical id 920.

Details
create table #temp
(
partid int,
maskid int,
chemicalid int,
status nvarchar(50)
)
insert into #temp
values
(100,1000,901,null),
(700,1700,909,null)
create table #parts
(
PartId int,
maskId int
)
insert into #parts (PartId,maskId)
values
(100,1000),
(200,1000),
(500,1700),
(600,1700),
(700,1700)
create table #Chemical
(
Chemicalmasterid int,
ChemicalId int,
PartId int
)
insert into #Chemical(Chemicalmasterid,ChemicalId,PartId)
values
(1 ,901,100),
(7 ,920,700)
final result
100 1000 901 conflict chemical id
700 1700 909 null

what i have tried

update t set status ='conflict chemical id' from #temp t

thank you for reply

1- I have temp table #temp have more than one parts as below :

create table #temp
(
partid int,
maskid int,
chemicalid int,
status nvarchar(50)
)
insert into #temp
values
(100,1000,901,null),
(700,1700,909,null)

2- i will get related part to parts exist on temp table #temp from tem table #parts based on MaskId

meaning when MaskId on temp table #parts have masked id on temp table #temp .

it will be as below :

PartId MaskId
200 1000
500 1700
600 1700

3-I will have third temp table chemical i will check if parts on temp table #temp or related parts I get from temp table #parts have same chemical Id that exist on temp table then status will be conflict as below

partid maskid chemical ststus
100 1000 901 conflict chemical id

meaning if chemicalid on temp table chemical same as chemicalid on temp table #temp or related

then conflict on status field

if different

no need update status exist on temp table #temp

final result (temp table #temp)
partid maskid chemicalid status
100 1000 901 conflict chemical id
700 1700 909 null

I explain steps above to get final result above .

thanks

1 Reply

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

ahmed elbarbary6y ago
thank you for reply
1- I have temp table #temp have more than one parts as below :
create table #temp
(
partid     int,
maskid     int,
chemicalid int,
status  nvarchar(50)
)
insert into #temp
values
(100,1000,901,null),
(700,1700,909,null)
2- i will get related part to parts exist on temp table #temp from tem table #parts based on MaskId
meaning when MaskId on temp table #parts have masked id on temp table #temp .
it will be as below :
PartId    MaskId
200       1000
500       1700
600       1700
3-I will have third temp table chemical i will check if parts on temp table #temp or related parts I get from temp table #parts have same chemical Id that exist on temp table then status will be conflict as below
partid maskid chemical ststus
100 1000 901 conflict chemical id
meaning if chemicalid on temp table chemical same as chemicalid on temp table #temp or related
then conflict on status field
if different
no need update status exist on temp table #temp
final result  (temp table #temp)
partid   maskid         chemicalid       status
100     1000             901              conflict chemical id
700     1700             909               null
I explain steps above to get final result above .
thanks