ahmed elbarbary

ahmed elbarbary

  • 977
  • 1.3k
  • 76.5k

How to update status with conflict data where chemical temp

Jan 26 2020 4:05 AM
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
  1. PartId MaskId ChemicalId  
  2. 200    1000      901  
  3. 500    1700      909  
  4. 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.
 
  1. Details  
  2. create table #temp  
  3. (  
  4. partid     int,  
  5. maskid     int,  
  6. chemicalid int,  
  7. status  nvarchar(50)  
  8. )  
  9. insert into #temp  
  10. values  
  11. (100,1000,901,null),  
  12. (700,1700,909,null)  
  13.   
  14. create table  #parts  
  15. (  
  16. PartId  int,  
  17. maskId  int  
  18. )  
  19.      
  20. insert into #parts (PartId,maskId)  
  21. values  
  22. (100,1000),  
  23. (200,1000),  
  24. (500,1700),  
  25. (600,1700),  
  26. (700,1700)  
  27.   
  28.   
  29. create table  #Chemical  
  30. (  
  31. Chemicalmasterid  int,  
  32. ChemicalId  int,  
  33. PartId   int  
  34. )                          
  35. insert into  #Chemical(Chemicalmasterid,ChemicalId,PartId)  
  36. values  
  37. (1 ,901,100),  
  38. (7 ,920,700)  
  39.   
  40. final result  
  41. 100     1000 901 conflict chemical id  
  42. 700     1700 909     null  
what i have tried
update t set status ='conflict chemical id' from #temp t

Answers (1)