1
Answer

How to delete child records from tradecode that not have par

ahmed elbarbary

ahmed elbarbary
4y
495
1
problem
How to delete child records from table tradecode that not have parent on tradecode ?
parent and child exist on table trade code based on table MappingCodeValue parent and child
so i need to delete records from trade code table that not have parent on table trade code
so according to my explain two rows 5,6 on trade code table will be deleted
 
  1. TradeCodeId  PartId CodeType   CodeValue      
  2. 5        1444   ECCS-URB    AB666-URB               
  3. 6        1931   ECCS-URB    AB778-URB  
 
so it wrong and i will delete it
but another rows on trade code have parent and child according to table mappingcodevalue so that it is correct
so how to write query delete rows that have rows that have child and not have parent from trade code
based on value exist on mappingcodevalue
  1. drop table #MappingCodeValue  
  2. drop table #TradeCode  
  3. create table #MappingCodeValue  
  4.  (  
  5.  id int identity (1,1),  
  6.  ParentCodeType  nvarchar(50),  
  7.  ParentCodeValue  nvarchar(50),  
  8.  ChildCodeType  nvarchar(50),  
  9.  ChildCodeValue  nvarchar(50)  
  10.  )  
  11.  INSERT INTO #MappingCodeValue  
  12.  (ParentCodeType,ParentCodeValue,ChildCodeType,ChildCodeValue)  
  13.  VALUES  
  14.  ('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),  
  15.  ('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),  
  16.  ('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),  
  17.  ('ECCS-US','AB778-US','ECCS-URB','AB778-URB') 
  18.   
  19.   
  20.  CREATE TABLE #TradeCode  
  21.  (  
  22.  TradeCodeId int identity(1,1),  
  23.  PartId  int,  
  24.  CodeType  nvarchar(50),  
  25.  CodeValue nvarchar(50)  
  26.  )  
  27.  insert into #TradeCode(PartId,CodeType,CodeValue)VALUES  
  28.  (1222,'ECCS-US','AB123-US'),  
  29.  (1255,'ECCS-US','AB555-US'),  
  30.  (1222,'ECCS-URB','AB123-URB'),  
  31.  (1255,'ECCS-URB','AB555-URB'),  
  32.  (1444,'ECCS-URB','AB666-URB'),  
  33.  (1931,'ECCS-URB','AB778-URB')  
 
Answers (1)
Next Recommended Forum